on April 28, 2009
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 > 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 > 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];
REGISTER