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 (https://darkslategrey-bat-805937.hostingersite.com)
