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.

SQL SERVER - Blocking Tree - Identifying Blocking Chain Using SQL Scripts blockingtree0-800x365


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
        ,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
WITH BlockingTree (spid, blocking_spid, [level], batch)
    SELECT   blc.spid
            ,CAST (REPLICATE ('0', 4-LEN (CAST (blc.spid AS VARCHAR))) + CAST (blc.spid AS VARCHAR) AS VARCHAR (1000)) AS [level]
    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)
    SELECT   blc.spid
            ,CAST(bt.[level] + RIGHT (CAST ((1000 + blc.SPID) AS VARCHAR (100)), 4) AS VARCHAR (1000)) AS [level]
    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

SQL SERVER - Blocking Tree - Identifying Blocking Chain Using SQL Scripts blockingtree

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)

, , , ,
Previous Post
SQL Terms vs MongoDB Terms
Next Post
SQL SERVER – sp_helpdb – Accidental Discovery

Related Posts

10 Comments. Leave new

  • Hi Pinal,
    Great post – thanks!

  • There is a logical error in the query. The query depends on the ObjectID to be unused in the current database scope when the locked object is in a different database. This may not always be true. See lines 44 to 48.

    Changing those lines as below corrects this issue.

    ,CASE WHEN spr.dbid = DB_ID()
    THEN OBJECT_NAME(sli.rsc_objid)
    ELSE ‘USE ‘+DB_NAME(spr.dbid)+’; SELECT ‘ +
    END AS [TableName]

  • Thanks Pinal !
    Is there any way to make this script works on AzureSQL database ? spt_value doesn’t exists

  • Hi Maxime and Pinal,
    I adjusted the script so it will run on all SQL Server versions from 2008 R2 (including Azure).
    Please feel free to use the script on your blog page.
    Script: https://pastebin.com/P84CX7pf

  • Awesome ! Thank you for sharing this

  • Hi Pinal,
    Thank you for updating the script on the blog page earlier today.

    When playing with the script and several databases, I saw that the script not always displayed the correct database, schema and table name.
    This is normally not a problem, but of course in some situations you may get into the problem where an SQL command is executed from database A and locks resources in database B.
    The script is now updated and I believe is handles most situations: https://pastebin.com/P84CX7pf

    The image (below the script) with the columns [ObjectID] and [TableName] is no longer correct, since these names now are correctly displayed .

    • Thank you Johnny for helping me improve this query. This looks amazing. I will run a test later today and will also update the image. You did fantastic in helping everyone.

  • Some SQL Server configurations have column names that are case sensitive. SPID vs spid, and BLOCKED vs blocked, and LEVEL vs level. I’ve updated my version and is running perfectly. Thank you soooo much!


Leave a Reply