What Is Running?
Dell was at my shop doing a review of several of our applications. The DBA they brought with them gave me this query. He called it "What Is Running" which is kind of a spin on the Adam Machanic sp_WhoIsActive. Anyway, this query is great for finding what is currently running in your system at the moment using the exec_request and exec_session DMVs. SP_WhoIsActive is great as well, but this seems to be a little more light weight and great to use in a jam. The score column is an indicator for the most resource intensive query.
SELECT
Sessions.session_id AS SessionID,
Sessions.login_name AS LoginName,
Sessions.host_name AS HostName,
Sessions.program_name AS ProgramName,
Sessions.client_interface_name AS ClientInterfaceName,
Requests.blocking_session_id,
Requests.wait_time AS WaitTime,
Requests.cpu_time AS CPUTime, Requests.total_elapsed_time AS ElapsedTime,
Requests.reads AS Reads, Requests.writes AS Writes, Requests.logical_reads AS LogicalReads,
Requests.row_count AS [RowCount], Requests.granted_query_memory*8 AS GrantedQueryMemoryKB,
(Requests.cpu_time+1)*(Requests.reads+Requests.writes+1) AS Score,
CASE WHEN sessions.transaction_isolation_level = 0 THEN 'Unspecified'
WHEN sessions.transaction_isolation_level = 1 THEN 'ReadUncomitted'
WHEN sessions.transaction_isolation_level = 2 THEN 'ReadCommitted'
WHEN sessions.transaction_isolation_level = 3 THEN 'Repeatable'
WHEN sessions.transaction_isolation_level = 4 THEN 'Serializable'
WHEN sessions.transaction_isolation_level = 5 THEN 'Snapshot'
END AS TransAction_Isolation_level,
Statements.text AS BatchText,
LEN(Statements.text) AS BatchTextLength, Requests.statement_start_offset/2 AS StartPos, Requests.statement_end_offset/2 AS EndPos,
CASE
WHEN Requests.sql_handle IS NULL THEN ' '
ELSE
SubString(
Statements.text,
(Requests.statement_start_offset+2)/2,
(CASE
WHEN Requests.statement_end_offset = -1 THEN LEN(CONVERT(nvarchar(MAX),Statements.text))*2
ELSE Requests.statement_end_offset
END - Requests.statement_start_offset)/2
)
END AS StatementText,
QueryPlans.query_plan AS QueryPlan
FROM
sys.dm_exec_sessions AS Sessions
JOIN sys.dm_exec_requests AS Requests ON Sessions.session_id=Requests.session_id
outer APPLY sys.dm_exec_sql_text(sql_handle) AS Statements -- user OUTER to get requests w/out this info
outer APPLY sys.dm_exec_query_plan(plan_handle) AS QueryPlans -- user OUTER to get requests w/out this info
WHERE Requests.session_id > 50
and Requests.session_id <> @@SPID -- ignore self
ORDER BY score DESC
SELECT
Sessions.session_id AS SessionID,
Sessions.login_name AS LoginName,
Sessions.host_name AS HostName,
Sessions.program_name AS ProgramName,
Sessions.client_interface_name AS ClientInterfaceName,
Requests.blocking_session_id,
Requests.wait_time AS WaitTime,
Requests.cpu_time AS CPUTime, Requests.total_elapsed_time AS ElapsedTime,
Requests.reads AS Reads, Requests.writes AS Writes, Requests.logical_reads AS LogicalReads,
Requests.row_count AS [RowCount], Requests.granted_query_memory*8 AS GrantedQueryMemoryKB,
(Requests.cpu_time+1)*(Requests.reads+Requests.writes+1) AS Score,
CASE WHEN sessions.transaction_isolation_level = 0 THEN 'Unspecified'
WHEN sessions.transaction_isolation_level = 1 THEN 'ReadUncomitted'
WHEN sessions.transaction_isolation_level = 2 THEN 'ReadCommitted'
WHEN sessions.transaction_isolation_level = 3 THEN 'Repeatable'
WHEN sessions.transaction_isolation_level = 4 THEN 'Serializable'
WHEN sessions.transaction_isolation_level = 5 THEN 'Snapshot'
END AS TransAction_Isolation_level,
Statements.text AS BatchText,
LEN(Statements.text) AS BatchTextLength, Requests.statement_start_offset/2 AS StartPos, Requests.statement_end_offset/2 AS EndPos,
CASE
WHEN Requests.sql_handle IS NULL THEN ' '
ELSE
SubString(
Statements.text,
(Requests.statement_start_offset+2)/2,
(CASE
WHEN Requests.statement_end_offset = -1 THEN LEN(CONVERT(nvarchar(MAX),Statements.text))*2
ELSE Requests.statement_end_offset
END - Requests.statement_start_offset)/2
)
END AS StatementText,
QueryPlans.query_plan AS QueryPlan
FROM
sys.dm_exec_sessions AS Sessions
JOIN sys.dm_exec_requests AS Requests ON Sessions.session_id=Requests.session_id
outer APPLY sys.dm_exec_sql_text(sql_handle) AS Statements -- user OUTER to get requests w/out this info
outer APPLY sys.dm_exec_query_plan(plan_handle) AS QueryPlans -- user OUTER to get requests w/out this info
WHERE Requests.session_id > 50
and Requests.session_id <> @@SPID -- ignore self
ORDER BY score DESC