Michael J. Swart

April 29, 2008

Who’s blocking who?

Filed under: Technical Articles — Michael J. Swart @ 10:50 am

A query that uses a CTE to determine who’s blocking who. I find it useful but a bit slow if things have gotten really really crazy.

SELECT spid, blocked INTO #tempSysProcesses FROM master..sysprocesses;

with BlockingChains (session_chains, leaf) AS

 --base case  
 select distinct cast(spid as varchar(max)), spid  
 from #tempSysProcesses with (nolock)  
 where spid >= 50
    and (blocked = 0 or blocked = spid)


    --recursive step
  select bc.session_chains + ', ' + 
        cast(sp.spid as varchar(max)),
    from #tempSysProcesses sp with (nolock)
    join BlockingChains bc
        on sp.blocked = bc.leaf
    where sp.blocked <> sp.spid
select leaf as session_id, session_chains as blocking_chain
from BlockingChains
where session_chains like '%,%'
order by leaf

DROP TABLE #tempSysProcesses

No Comments »

No comments yet.

RSS feed for comments on this post. TrackBack URL

Leave a comment

Powered by WordPress