SQL 2005 Blocking Chains – a Friendly Display Using CTE and Recursion

Originally published Thursday, April 03, 2008 at http://www.biblereadingschedule.com/nucleus?itemid=80&catid=6

Update 4/4/2008 – I tested this on SQL Server 2008, the Feb. 2008 CTP (CTP 6), and the script works great!

SQLServerCentral.com had a link to an interesting article, “Find blocking processes using recursion in SQL Server 2005.” For an explanation of the basic script functionality, please see that article.

I decided to see if I could modify the script to output the blocking chain information in a format more to my liking. You can use this for quick diagnosis of blocking problems.

So I am going to give you the code to create a few test tables and to create blocks. Finally I will give you my version of the script that displays the blocking chain. It is recommended that you display the results of the blocking chain script to text rather than grid. The script only works with SQL 2005 and above.

First the script to create some test tables:

Here is the script. To easily copy the code, hover your mouse over the below section and click the “copy to clipboard” button in the tool bar that appears at the top right. If the copy button is not available, then click the “view source” button, press Ctrl + A to select all, and Ctrl + C to copy. Then paste into your favorite editor.

--Create some test tables to create blocking against

CREATE TABLE [dbo].[Table_A](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[Code] [varchar](50) NULL,
 CONSTRAINT [PK_Table_A] PRIMARY KEY CLUSTERED
(
	[ID] ASC
)
)
ON [PRIMARY]

GO

CREATE TABLE [dbo].[Table_B](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[Code] [varchar](50) NULL,
 CONSTRAINT [PK_Table_B] PRIMARY KEY CLUSTERED
(
	[ID] ASC
)
)
ON [PRIMARY]

GO

CREATE TABLE [dbo].[Table_C](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[Code] [varchar](50) NULL,
 CONSTRAINT [PK_Table_C] PRIMARY KEY CLUSTERED
(
	[ID] ASC
)
)
ON [PRIMARY]

GO

 

Run this from the first query window to begin the blocking chain. This will open an un-committed transaction while holding an exclusive table lock against Table_A:

BEGIN TRAN

SELECT * FROM Table_A WITH (TABLOCKX)

--COMMIT TRAN --We will commit the tran later to end the blocking

 

Run this from a second query window. This will be blocked by the first query, and will acquire an exclusive lock on Table_B as well:

SELECT * FROM Table_A a
INNER JOIN Table_B b WITH (TABLOCKX)
ON a.ID = b.ID

 

Run this from a third query window. This will be blocked by the second query:

SELECT * FROM Table_B b
INNER JOIN Table_C c
ON b.ID = c.ID

 

Finally here is the modified blocking chain script. It will produce output similar to the following:

****Head of Blocking Chain SPID 56...
SPID 56 (DB: TestDb)  Statement:   BEGIN TRAN  SELECT * FROM Table_A WITH (TABLOCKX)  --COMMIT TRAN --We will commit the tran later to end the blocking
...is blocking the following SPID(s):

     57 (DB: TestDb)  Statement: SELECT * FROM Table_A a INNER JOIN Table_B b WITH (TABLOCKX) ON a.ID = b.ID 

          SPID 57 is, in turn, blocking the following SPID:
               SPID 58 (DB: TestDb)  Statement: SELECT * FROM Table_B b INNER JOIN Table_C c ON b.ID = c.ID

--Best to output results to text rather than grid -- SQL 2005 and above only

SET NOCOUNT ON

IF OBJECT_ID('tempdb..#Processes') IS NOT NULL
	DROP TABLE #Processes

DECLARE @results TABLE (
	id INT IDENTITY(1,1),
	DB_Name VARCHAR(500),
	BlockingSPID INT, SPID INT, 
	BlockingStatement VARCHAR(MAX), 
	RowNo INT, LevelRow INT
)

SELECT
	s.spid, 
	BlockingSPID = s.blocked, 
	DatabaseName = DB_NAME(s.dbid),
	s.program_name, 
	s.loginame, 
	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, BlockingStatement, RowNo, LevelRow, DB_Name)
AS
(
	SELECT
		s.SPID, 
		s.BlockingSPID, 
		s.Definition, 
		ROW_NUMBER() OVER(ORDER BY s.SPID) AS RowNo,
		0 AS LevelRow,
		s.DatabaseName AS DB_Name
	FROM #Processes s
		INNER JOIN #Processes s1 ON s.SPID = s1.BlockingSPID
	WHERE s.BlockingSPID = 0
		UNION ALL
	SELECT
		r.SPID,
		r.BlockingSPID, 
		r.Definition,
		d.RowNo,
		d.LevelRow + 1,
		r.DatabaseName AS DB_Name
	FROM #Processes r
		INNER JOIN Blocking d ON r.BlockingSPID = d.SPID
	WHERE r.BlockingSPID > 0
)
INSERT INTO @results (
	[DB_Name],
	[BlockingSPID],
	[SPID],
	[BlockingStatement],
	[RowNo],
	[LevelRow]
) 
SELECT 
	MIN(DB_NAME) AS DB_Name,
	BlockingSPID, 
	SPID, 
	BlockingStatement, 
	MIN(RowNo), 
	LevelRow 
FROM Blocking
GROUP BY BlockingSPID, SPID, BlockingStatement, LevelRow
ORDER BY MIN(RowNo), LevelRow
 
SELECT 
	CASE
		WHEN [BlockingSPID] = 0 
		THEN '
****Head of Blocking Chain SPID ' + CAST([SPID] AS VARCHAR(50)) + '...
'
			+ 'SPID ' + CAST([SPID] AS VARCHAR(50)) + ' (DB: ' + [DB_Name] + ') ' + ' Statement: ' + REPLACE(REPLACE([BlockingStatement], CHAR(10),' '), CHAR(13), '')
				+ '
'
				+ '...is blocking the following SPID(s):'
		WHEN [LevelRow] > 1
		THEN '
'
			+ SPACE(LevelRow * 5) + 'SPID ' 
			+ CAST(BlockingSPID AS VARCHAR(50))
			+ ' is, in turn, blocking the following SPID:
'
			+ SPACE((LevelRow + 1) * 5) + 'SPID ' + CAST([SPID] AS VARCHAR(50)) + ' (DB: ' + [DB_Name] + ') ' + ' Statement: ' + REPLACE(REPLACE([BlockingStatement], CHAR(10),' '), CHAR(13), '')

		ELSE '
'
			+ SPACE(LevelRow * 5) + CAST([SPID] AS VARCHAR(50)) + ' (DB: ' + [DB_Name] + ') ' + ' Statement: ' + REPLACE(REPLACE([BlockingStatement], CHAR(10),' '), CHAR(13), '')
				+ '
'
	END 
FROM @results

IF NOT EXISTS (SELECT TOP 1 * FROM @results)
	PRINT 'No Blocking Chains Were Found'

BEGIN TRY
	DROP TABLE #Processes
END TRY 
BEGIN CATCH
END CATCH

 

Finally, you can end the blocking by going back to the first query window and running the “COMMIT TRAN” statement (commented out). This will allow the second and third queries to complete. In the real world you may decide to kill the SPID at the head of a blocking chain.

Posted November 15, 2010 by Norm Enger in Microsoft SQL Server