The next updates I'll probably make will be to display only the top-level blockers rather than all blocking chains, and to get rid of the 0s at the end of each blocking chain line.
Here's the SQL:
if object_id('tempdb..#blockers0') is not null
drop table #blockingchain
create table #blockingchain
(
blockingchain varchar(255)
)
insert into #blockingchain
select cast(spid as varchar(5)) + ' -> ' + cast(blocked as varchar(5))
from master..sysprocesses
where blocked <> 0
while exists (select 1 from #blockingchain where cast((substring(right(blockingchain, 5), 1 + charindex('>', right(blockingchain, 5)), 5)) as smallint) <> 0)
begin
update bc set blockingchain = blockingchain + ' -> ' + cast(sp.blocked as varchar(5))
from #blockingchain bc
inner join master..sysprocesses sp
on cast((substring(right(bc.blockingchain, 5), 1 + charindex('>', right(bc.blockingchain, 5)), 5)) as smallint) = sp.spid
end
select * from #blockingchain
order by len(blockingchain) asc,
substring(blockingchain, 1, charindex('-', blockingchain)) asc
Sample output:
1 comment:
Good idea. This is also a great use for a recursive CTE
with blocked as
(
select
session_id
, blocking_session_id
, chain = convert(varchar(100), convert(varchar(10), session_id) + ' -> ' + convert(varchar(10), blocking_session_id))
, 1 as level
from sys.dm_exec_requests
where blocking_session_id <> 0
union all
select
b.session_id
, r.blocking_session_id
, chain = convert(varchar(100), b.chain + ' -> ' + convert(varchar(10), r.blocking_session_id))
, b.level + 1 as level
from blocked b
inner join sys.dm_exec_requests r
on b.blocking_session_id = r.session_id
and r.blocking_session_id <> 0
)
select b.chain
from blocked b
where not exists (select 'x' from blocked
where session_id = b.session_id
and level > b.level)
order by b.level, b.session_id;
Post a Comment