“What’s running long right now in the server? Any blocks?”

Question that I am asked so frequently, but thanks to some shiny new monitoring I have to run this less and less. I’m not sure of the true source of this query, but it was given to me at work shortly after I started at this job and I held on to it for dear life.

USE [master]
go

--Show Process List

SELECT SPID = er.session_id
, ot.Threads
, RunningThreads = coalesce(rsp.RunningThreads,0)
, Pct_Comp = er.percent_complete
, Est_Comp_Time = CASE er.estimated_completion_time WHEN cast(0 as bigint) THEN NULL ELSE dateadd(minute, (er.estimated_completion_time/60000), getdate()) END
, er.status
, er.command
, database_name = sd.name
, BlockedBy = wt.blocking_session_id
, HeadBlocker = coalesce(hb5.session_id, hb4.session_id, hb3.session_id, hb2.session_id, hb1.session_id, wt.blocking_session_id)
, wait_type = coalesce(CASE er.wait_type WHEN 'CXPACKET' THEN 'CXPACKET - ' + sp.LastWaitType1 ELSE sp.LastWaitType1 END, lower(er.last_wait_type)) --Lowercase denotes it's not currently waiting, also noted by a wait time of 0.
, Wait_Time_Sec = Cast(er.wait_time/1000.0 as DEC(20,3))
, er.wait_resource
, Duration_Sec = Cast(DATEDIFF(s, er.start_time, GETDATE()) as DEC(20,0))
, CPU_Sec = Cast(er.cpu_time/1000.0 as DEC(20,3))
, LogicalReads_K = Cast(er.logical_reads/1000.0 as DEC(20,0))
, PhysicalReads_K = Cast(er.reads/1000.0 as DEC(20,3))
, Writes_K = Cast(er.writes/1000.0 as DEC(20,3))
, [Statement] = SUBSTRING (st.text, er.statement_start_offset/2+1,
abs(CASE WHEN er.statement_end_offset = -1
THEN LEN(CONVERT(NVARCHAR(MAX), st.text)) * 2
ELSE er.statement_end_offset END - er.statement_start_offset)/2+1)
, st.text as Query
, es.login_time
, es.host_name
, program_name = CASE LEFT(es.program_name, 29)
WHEN 'SQLAgent - TSQL JobStep (Job '
THEN 'SQLAgent Job: ' + (SELECT name FROM msdb..sysjobs sj WHERE substring(es.program_name,32,32)=(substring(sys.fn_varbintohexstr(sj.job_id),3,100))) + ' - ' + SUBSTRING(es.program_name, 67, len(es.program_name)-67)
ELSE es.program_name
END
, es.client_interface_name
, es.login_name
, es.status
, es.total_scheduled_time
, es.total_elapsed_time
, er.start_time
, es.last_request_start_time
, es.last_request_end_time
, cursor_statement = cst.text
, cursor_create = c.creation_time
, cursor_dormant = c.dormant_duration
, Req_memory_mb = mg.requested_memory_kb / 1024
, Grant_memory_mb = mg.granted_memory_kb / 1024
, Memory_wait_sec = cast(mg.wait_time_ms / 1000.0 as DEC(20,1))
, qp.query_plan
FROM sys.dm_exec_requests (nolock) er
INNER JOIN sys.dm_exec_sessions (nolock) es on er.session_id = es.session_id
LEFT JOIN sys.databases (nolock) sd on er.database_id = sd.database_id
INNER JOIN (SELECT session_id, count(1) Threads FROM sys.dm_os_tasks (nolock) GROUP BY session_id) ot on er.session_id = ot.session_id
LEFT JOIN (SELECT spid, LastWaitType1 = MIN(lastwaittype), LastWaitType2 = MAX(lastwaittype) FROM master.dbo.sysprocesses (nolock) sp WHERE waittime > 0 AND lastwaittype <> 'cxpacket' GROUP BY spid) sp ON er.session_id = sp.spid
LEFT JOIN (SELECT spid, RunningThreads = COUNT(1) FROM master.dbo.sysprocesses (nolock) sp WHERE waittime = 0 GROUP BY spid) rsp ON er.session_id = rsp.spid
LEFT JOIN (SELECT spid, CPU_Sec = Cast(Sum(sp.cpu/1000.0) as DEC(20,3)) FROM master.dbo.sysprocesses (nolock) sp GROUP BY spid) spCPU ON er.session_id = spCPU.spid
LEFT JOIN (SELECT session_id, max(blocking_session_id) blocking_session_id FROM sys.dm_os_waiting_tasks (nolock) wt WHERE wt.blocking_session_id <> wt.session_id GROUP BY session_id) wt ON er.session_id = wt.session_id
LEFT JOIN (SELECT session_id, max(blocking_session_id) blocking_session_id FROM sys.dm_os_waiting_tasks (nolock) wt GROUP BY session_id) hb1 ON wt.blocking_session_id = hb1.session_id
LEFT JOIN (SELECT session_id, max(blocking_session_id) blocking_session_id FROM sys.dm_os_waiting_tasks (nolock) wt GROUP BY session_id) hb2 ON hb1.blocking_session_id = hb2.session_id
LEFT JOIN (SELECT session_id, max(blocking_session_id) blocking_session_id FROM sys.dm_os_waiting_tasks (nolock) wt GROUP BY session_id) hb3 ON hb2.blocking_session_id = hb3.session_id
LEFT JOIN (SELECT session_id, max(blocking_session_id) blocking_session_id FROM sys.dm_os_waiting_tasks (nolock) wt GROUP BY session_id) hb4 ON hb3.blocking_session_id = hb4.session_id
LEFT JOIN (SELECT session_id, max(blocking_session_id) blocking_session_id FROM sys.dm_os_waiting_tasks (nolock) wt GROUP BY session_id) hb5 ON hb4.blocking_session_id = hb5.session_id
LEFT JOIN sys.dm_exec_cursors(0) c ON er.session_id = c.session_id
LEFT JOIN sys.dm_exec_query_memory_grants (nolock) mg ON er.session_id = mg.session_id
CROSS APPLY sys.dm_exec_sql_text(er.sql_handle) AS st
OUTER APPLY sys.dm_exec_sql_text(c.sql_handle) AS cst
OUTER APPLY sys.dm_exec_query_plan(er.plan_handle) qp
WHERE er.session_id <> @@SPID
ORDER BY er.percent_complete DESC, er.cpu_time DESC, er.session_id