The DBA Sees Your Blocking Spids… A Bird’s Eye Summary of Blocking
Update from Kendra (Nov 2018)
I’m keeping this post for posterity, but I REALLY don’t recommend the script. You’d be much better off using a production monitoring tool that did this job, or @AdamMachanic ‘s sp_WhoIsActive.
Maybe you’re a user in a reporting database running a long query in the read committed isolation level, merrily blocking a process which needs to load data.
Maybe you’re an application misbehaving in a transactional front end database, blocking other applications.
Or possibly you’re a middle tier application who is behaving well and trying to do a small update, but the table you’re updating holds a legacy trigger designed when it was a much smaller table, and that trigger is doing data validation with long running locks.
With there being a lot of users, a lot of applications, and in general a lot of blocking, it can be useful to get a bird’s eye view.
I manage a system where we frequently used to see lots of blocking related to triggers. We’d see one spid block another, and then that spid block a few more, and so on. We would also occasionally see a middle tier application open a connection and start doing work, then stop activity but leave a transaction open. In that case it could hold locks until the connection terminated, even though the spid was sleeping.
So it was extremely useful to be able to see quickly who the spids were at the root of the blocking trees. There would often be a very large number of connections open sp_who2 wasn’t the easiest method.
I also like to collect the execution plans for blockers, which makes it much easier to track down the cause.
Limitations: There are limits on recursion, so if there is a super-massive, huge amount of blocking, this script won’t always give results.
Hence, this script…
SET NOCOUNT ON GO declare @Processes table ( spid int , BlockingSPID int , dbid int ); INSERT @Processes (spid, BlockingSPID, dbid) SELECT s.spid , BlockingSPID = s.blocked , s.dbid FROM sys.sysprocesses s WHERE s.spid &gt; 50; WITH Blocking(SPID, BlockingSPID, [dbid], RowNum, [Rank]) AS ( SELECT s.SPID , s.BlockingSPID , s.dbid , RowNum = ROW_NUMBER() OVER(ORDER BY s.SPID) , [Rank] = 0 FROM @Processes s WHERE s.BlockingSPID = 0 AND EXISTS( select s1.spid from @Processes s1 where s.SPID = s1.BlockingSPID) -- anchor those who are blocked UNION ALL SELECT r.SPID , r.BlockingSPID , r.dbid , d.RowNum , [Rank]=d.[Rank] + 1 FROM @Processes r JOIN Blocking d ON r.BlockingSPID = d.SPID WHERE r.BlockingSPID &gt; 0 --Those who are blocked ) SELECT RootOfEvil = CASE when bl.BlockingSpid = 0 THEN 'Y' ELSE '' END , SPID = cast(bl.SPID AS NVARCHAR(10)) , BlockedBy= CASE when bl.BlockingSpid = 0 THEN '' ELSE CAST(bl.BlockingSpid as nvarchar(10)) END , se.status , db=db_name(bl.dbid) , isolation_level = CASE se.transaction_isolation_level WHEN 1 then 'ReadUncomitted' WHEN 2 then 'ReadCommitted' WHEN 3 then 'Repeatable' WHEN 4 then 'Serializable' WHEN 5 then 'Snapshot' ELSE 'Unknown' END , se.login_name , se.nt_user_name , se.host_name , se.client_interface_name , se.lock_timeout , se.logical_reads , se.reads , se.writes , rq.command , wait_type=coalesce(rq.wait_type, rq.last_wait_type) , waiting_minutes=cast(rq.wait_time/1000./60. as int) , executing_text= coalesce( CASE tx.encrypted WHEN 1 THEN 'Encrypted' ELSE CASE WHEN bl.BlockingSpid=0 then tx.text ELSE convert(nvarchar(250), tx.text) END END , convert(nvarchar(250),se.status)) , [plan]=pl.query_plan FROM Blocking bl JOIN sys.dm_exec_connections c on c.session_id = bl.spid LEFT JOIN sys.dm_exec_requests rq on c.session_id=rq.session_id LEFT JOIN sys.dm_exec_sessions se on c.session_id=se.session_id OUTER APPLY sys.dm_exec_sql_text(c.most_recent_sql_handle) tx OUTER APPLY sys.dm_exec_query_plan(rq.plan_handle) pl ORDER BY RowNum, [Rank];