As with every good DBA's toolbox, my blocking list query using sys.dm_exec_requests is evolving.
I've added the following features:
- The query execution plan of the active request in the QueryPlan column to the right.
- Variable at the top which changes the relationship of the join between sys.dm_exec_sessions and sys.dm_exec_requests.
- When set to 0, this query now displays all sessions, even those without active requests. I recently found this helpful when researching sleeping sessions that were blocking active sessions.
- When set to 1, this query displays as it used to - only session and active request data.
- Percent_Complete column - great for finding the progress of backup and restores
- A few other minor helpful columns
declare @showallspids bit = 1
create table #ExecRequests (
id int IDENTITY(1,1) PRIMARY KEY
, session_id smallint not null
, request_id int null
, request_start_time datetime null
, login_time datetime not null
, status nvarchar(60) null
, command nvarchar(32) null
, sql_handle varbinary(64) null
, statement_start_offset int null
, statement_end_offset int null
, plan_handle varbinary (64) null
, database_id smallint null
, user_id int null
, blocking_session_id smallint null
, wait_type nvarchar (120) null
, wait_time_s int null
, wait_resource nvarchar(120) null
, cpu_time_s int null
, tot_time_s int null
, reads bigint null
, writes bigint null
, logical_reads bigint null
, [host_name] nvarchar(256) null
, [program_name] nvarchar(256) null
, blocking_these varchar(1000) NULL
, percent_complete int null
)
insert into #ExecRequests (session_id,request_id, request_start_time, login_time, status,command,sql_handle,statement_start_offset,statement_end_offset,plan_handle,database_id,user_id,blocking_session_id,wait_type,wait_time_s,wait_resource,cpu_time_s,tot_time_s,reads,writes,logical_reads,[host_name], [program_name] )
select s.session_id,request_id, r.start_time, s.login_time, r.status,command,sql_handle,statement_start_offset,statement_end_offset,plan_handle,s.database_id,user_id,blocking_session_id,wait_type,r.wait_time/60.,r.wait_resource ,r.cpu_time/60.,r.total_elapsed_time/60.,r.reads,r.writes,r.logical_reads,s.[host_name], s.[program_name]
from sys.dm_exec_sessions s
left outer join sys.dm_exec_requests r on r.session_id = s.session_id
where 1=1
and r.session_id > 35 --retrieve only user spids
and r.session_id <> @@SPID --ignore myself
and (@showallspids = 1 or r.session_id is not null)
update #ExecRequests
set blocking_these = LEFT((select isnull(convert(varchar(5), er.session_id),'') + ', '
from #ExecRequests er
where er.blocking_session_id = isnull(#ExecRequests.session_id ,0)
and er.blocking_session_id <> 0
FOR XML PATH('')
),1000)
select * from
(
select
r.session_id , r.host_name , r.program_name
, r.status
, r.blocking_these
, blocked_by = r.blocking_session_id
, r.wait_type , r.wait_resource
, DBName = db_name(r.database_id)
, r.command
, login_time
, request_start_time
, r.tot_time_s, r.wait_time_s, r.cpu_time_s, r.reads, r.writes, r.logical_reads
--, [fulltext] = est.[text]
, offsettext = CASE WHEN r.statement_start_offset = 0 and r.statement_end_offset= 0 THEN NULL
ELSE SUBSTRING ( est.[text]
, r.statement_start_offset/2 + 1,
CASE WHEN r.statement_end_offset = -1 THEN LEN (CONVERT(nvarchar(max), est.[text]))
ELSE r.statement_end_offset/2 - r.statement_start_offset/2 + 1
END )
END
, r.statement_start_offset, r.statement_end_offset
, cacheobjtype = LEFT (p.cacheobjtype + ' (' + p.objtype + ')', 35)
, QueryPlan = qp.query_plan
from #ExecRequests r
LEFT OUTER JOIN sys.dm_exec_cached_plans p ON p.plan_handle = r.plan_handle
OUTER APPLY sys.dm_exec_query_plan (r.plan_handle) qp
OUTER APPLY sys.dm_exec_sql_text (r.sql_handle) est
) a
order by LEN(blocking_these) desc, blocking_these desc, blocked_by desc, session_id asc
drop table #ExecRequests
GO
