Tuesday, January 30, 2007

Blocking Chain script

I had a need to follow some quickly changing blocking chains today, and couldn't find any simple blocking chain scripts that I liked, so I wrote one. It's not the prettiest method, but it's simple, easy to modify, and doesn't rely on lots of hard-to-debug dynamic SQL.

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:

Michael said...

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;