SQL SERVER – Blocking Tree – Identifying Blocking Chain Using SQL Scripts

When I start working on the Comprehensive Database Performance Health Check with my client, I usually have no idea where will I end up while looking for the root cause of the performance problem for my client. However, whenever I end up on a situation where we have lots of blocking issues with my client, I always depend on the script which I have written earlier on about the Blocking Tree. Let us discuss that today.

Story of Blocking Chain

A few years ago, I wrote the script which will give me lead blockers for my query on this blog post: SQL SERVER – Identifying Blocking Chain Using SQL Scripts. The blog post got very popular as it was very easy to use the script. During this year, I was very fortunate that I got much help from various experts in modifying the script to make it more meaningful. I must thank two SQL Server Experts specifically – Braden and JohnnyB.

I have been using the script in my consultation which is mixed with all the suggestions in the comments and a few of my own modifications. However, when I saw the recent modification of SQL Server Expert JohnnyB, I realized that he filled up one of the biggest gaps which I have personally experienced with the script and that is while dealing with the applications using the cursors.

When debugging the application with the cursors, we normally only get the SQL statement “FETCH API_CURSOR…”, which doesn’t tell the real SQL statement run by the cursor. This was a huge issue but I never got around to address the issue personally. Thankfully JohnnyB took time to improve the script of Branden and make this script more useful.

One Modification: There is a small limitation of the current script as well. The current script will only show the table name if you are running the script with the current database content. What I mean is that if you are running this script for DatabaseA and if the TableName column belongs to the databases, you will see the table name but if it belongs to another database, you will see the NULL value. In this scenario, I have modified the script of JohnnyB and added the command which you can run on your query window and get the name of the Schema and TableName.

Blocking Tree Script

Well, here is a newly updated script that will display a blocking tree.

/*  SQL Blocking Tree w/Cursor info
Thanks SQL Server Expert JOHNNYBNO 
*/
IF OBJECT_ID('tempdb..#Blocks') IS NOT NULL
    DROP TABLE #Blocks
SELECT   spid
        ,blocked
        ,REPLACE (REPLACE (st.TEXT, CHAR(10), ' '), CHAR (13), ' ' ) AS batch
INTO     #Blocks
FROM     sys.sysprocesses spr
	CROSS APPLY sys.dm_exec_sql_text(spr.SQL_HANDLE) st
GO
 
WITH BlockingTree (spid, blocking_spid, [level], batch)
AS
(
    SELECT   blc.spid
            ,blc.blocked
            ,CAST (REPLICATE ('0', 4-LEN (CAST (blc.spid AS VARCHAR))) + CAST (blc.spid AS VARCHAR) AS VARCHAR (1000)) AS [level]
            ,blc.batch
    FROM    #Blocks blc
    WHERE   (blc.blocked = 0 OR blc.blocked = SPID) AND
			EXISTS (SELECT * FROM #Blocks blc2 WHERE blc2.BLOCKED = blc.SPID AND blc2.BLOCKED <> blc2.SPID)
    UNION ALL
    SELECT   blc.spid
            ,blc.blocked
            ,CAST(bt.[level] + RIGHT (CAST ((1000 + blc.SPID) AS VARCHAR (100)), 4) AS VARCHAR (1000)) AS [level]
            ,blc.batch
    FROM     #Blocks AS blc
		INNER JOIN BlockingTree bt 
			ON	blc.blocked = bt.SPID
    WHERE   blc.blocked > 0 AND
			blc.blocked <> blc.SPID
)
SELECT	N'' + ISNULL(REPLICATE (N'|         ', LEN (LEVEL)/4 - 2),'')
        + CASE WHEN (LEN(LEVEL)/4 - 1) = 0 THEN '' ELSE '|------  ' END
        + CAST (bt.SPID AS NVARCHAR (10)) AS BlockingTree
        ,spr.lastwaittype   AS [Type]
        ,spr.loginame       AS [Login Name]
        ,DB_NAME(spr.dbid)  AS [Source database]
        ,st.text            AS [SQL Text]
		,CASE WHEN cur.sql_handle IS NULL THEN '' ELSE (SELECT [TEXT] FROM sys.dm_exec_sql_text (cur.sql_handle)) END  AS [Cursor SQL Text]
        ,DB_NAME(sli.rsc_dbid)  AS [Database]
		,OBJECT_SCHEMA_NAME(sli.rsc_objid,sli.rsc_dbid) AS [Schema]
		,OBJECT_NAME(sli.rsc_objid, sli.rsc_dbid) AS [Table]
        ,spr.waitresource   AS [Wait Resource]
        ,spr.cmd            AS [Command]
        ,spr.program_name   AS [Application]
        ,spr.hostname       AS [HostName]
        ,spr.last_batch     AS [Last Batch Time]
FROM BlockingTree bt
	LEFT OUTER JOIN sys.sysprocesses spr 
		ON	spr.spid = bt.spid
	CROSS APPLY sys.dm_exec_sql_text(spr.SQL_HANDLE) st
	LEFT JOIN sys.dm_exec_cursors(0) cur
		ON	cur.session_id = spr.spid AND
			cur.fetch_status != 0
	JOIN sys.syslockinfo sli
		ON	sli.req_spid = spr.spid AND
			sli.rsc_type = 5 AND
			OBJECT_NAME(sli.rsc_objid, sli.rsc_dbid) IS NOT NULL
ORDER BY LEVEL ASC

Well, there you go, when you run the above script it will give similar results to the following image. If you like the script, do not forget to thank SQL Server Experts Branden and particularly JohnnyB who made this new script possible.

Update 1: Thanks you SQL Server Expert Robert, based on your comment I have modified the code to display the object name.

Reference: Pinal Dave (http://blog.SQLAuthority.com)