Catch the Lead SQL Blocker - the real culprit of system slowness

Your client made an urgent call to you, said that the software system is hang. You check the CPU, RAM, I/O, and network, in all production servers, all seems healthy. You try to access the software system, but it's inaccessible. In this case, I suggest you to check the SQL Server, there's a free tool that I love so much called Idera SQL Check. If you see the Processes window just like a spider web, then you find the cause - SQL process blocking.

It is very common to deal with blocking scenario and there may be a blocking chain means one process (aka SPID) is blocking second one and second one is blocking third one and so on. It's really difficult to track the root of blocking chain by simply running the system stored procedure sp_who2 or from SSMS activity monitor if there are number of SPIDs which are involved. So here I wish to share a stored procedure that I wrote which will help you to find out the root SPID and all associated details, in order to help you to further troubleshoot and make prompt decision (may be you will consider to kill the lead blocker for a quick fix for the incident).

USE [master]
GO

CREATE PROC [dbo].[ViewProcessBlocking]
AS
BEGIN
SET NOCOUNT ON;

SELECT
s.spid, Blockingspid = s.blocked, DatabaseName = DB_NAME(s.dbid),
s.program_name, s.loginame, s.hostname, s.login_time, s.last_batch, s.waittime, s.waitresource,
ObjectName = OBJECT_NAME(objectid, s.dbid), Definition = CAST(text AS VARCHAR(MAX))
INTO #Processes
FROM sys.sysprocesses s
CROSS APPLY sys.dm_exec_sql_text (sql_handle)
WHERE
s.spid > 50
;
WITH Blocking(spid, Blockingspid, DB, BlockingObject, [Blocking Statement / Definition], program, login, host, login_time, last_batch, waittime_ms, waitresource, RowNo, LevelRow)
AS
(
SELECT
s.spid, s.Blockingspid, s.DatabaseName, s.ObjectName, s.Definition,
s.program_name, s.loginame, s.hostname, s.login_time, s.last_batch, s.waittime, s.waitresource,
ROW_NUMBER() OVER(ORDER BY s.spid),
0 AS LevelRow
FROM
#Processes s
JOIN #Processes s1 ON s.spid = s1.Blockingspid
WHERE
s.Blockingspid = 0
UNION ALL
SELECT
r.spid, r.Blockingspid, r.DatabaseName, r.ObjectName, r.Definition,
r.program_name, r.loginame, r.hostname, r.login_time, r.last_batch, r.waittime, r.waitresource,
d.RowNo,
d.LevelRow + 1
FROM
#Processes r
JOIN Blocking d ON r.Blockingspid = d.spid
WHERE
r.Blockingspid > 0
)
SELECT *
INTO #BlockTree
FROM Blocking
ORDER BY RowNo, LevelRow

-- Top HEAD
SELECT TOP 1 'Oldest HEAD may need to KILL' AS OldestHeadMayKill, 'KILL ' + CAST(spid AS varchar(10)) AS KillStmt,
* INTO #TopHead FROM #BlockTree WHERE LevelRow = 0 ORDER BY last_batch
SELECT * FROM #TopHead
SELECT COUNT(DISTINCT spid) AS [Total no. of affected process], CAST(MAX(waittime_ms) / 1000.0 / 60.0 AS decimal(38, 1)) AS [Longest Wait Minute(s)] FROM #BlockTree
-- All blocker(s) & blocked
SELECT DISTINCT
spid, Blockingspid, DB, BlockingObject, [Blocking Statement / Definition], program, [login], host, login_time, last_batch, waittime_ms, waitresource
FROM #BlockTree

DROP TABLE #TopHead
DROP TABLE #BlockTree
DROP TABLE #Processes

END

GO


Run this stored procedure:
EXEC master..ViewProcessBlocking
The result will be like this:

It shows you the lead blocker SPID, what SQL its running, how many processes are being blocked and how long it is. There's also a KILL statement to kill that culprit. As your blocking scenario may be caused by multiple lead blockers, you many need to kill and run ViewProcessBlocking multiple times in order to settle down the incident. Remind that it's just a quick fix to kill the lead blocker(s), as a DBA you must further investigate the problem. You can refer to my older post to enable the tracking events of blocked process and deadlock.