Dynamic Management Views and Dynamic Management Functions - Microsoft SQL Server 2012 Performance Tuning - page 143

SELECT
S.host_name AS ClientMachine
,S.program_name AS ApplicationName
,S.original_login_name AS LoginName
,C.name AS CursorName
,C.properties AS CursorOptions
,C.creation_time AS CursorCreatinTime
,ST.text AS SQLQuery
,C.is_open AS IsCursorOpen
,C.worker_time/1000 AS DurationInMiliSeconds
,C.reads AS NumberOfReads
,C.writes AS NumberOfWrites
FROM sys.dm_exec_cursors(0) AS C
INNER JOIN sys.dm_exec_sessions AS S
ON C.session_id = S.session_id
CROSS APPLY sys.dm_exec_sql_text(C.sql_handle) AS ST
ORDER BY DurationInMiliSeconds DESC
GO
How it works...
In this recipe the first query uses the following DMVs and DMFs:
F sys.dm_exec_requests
F sys.dm_exec_sessions
F sys.dm_exec_sql_text
F sys.dm_exec_query_plan
In this query, we specified numerous columns that provide great amount of details on current
requests. The following are the questions whose answers we can determine about a particular
request by examining the result of the query:
F Which database is supposed to handle the request?
F Which login is executing the request?
F From which computer has the request arrived?
F From which application has the request been initiated?
F When did the request arrive?
F What SQL statements are being executed in the request?
F What is the execution plan for the SQL statements?
F What is the time duration since the request has been running?
F Did the request open any transaction?