Use DBCC PAGE to identify rows involved in blocking operations in SQL Server

Recently, one of my customers complained about a BizFlow database that was “running slowly”. BizFlow uses SQL Server to store data used in business processes – in this implementation there is a main BizFlow database, and an “archive” database.

The system I was looking at has a Windows Service that moves old rows from the main BizFlow database into the archive database based on certain aspects of the rows involved.

Symptoms the customer was complaining about included:

  • SQL Server maintenance operations, such as index rebuilds, were taking too long
  • Access to certain client screens were either returning errors or were taking a very long time to open
  • DTS processes where returning errors to other infrastructure services, causing various business issues

My first assumption, based on the symptoms they were experiencing, indicated this was likely to be a fairly standard blocking problem.

I used the standard dynamic-management view to see if there were any sessions blocked:

SELECT * 
FROM sys.dm_exec_requests der 
WHERE der.blocking_session_id <> 0

There were at least 50 sessions being blocked. Tracing the blocking tree back to the root blocker identified the bizflow_archive login, which is used exclusively by, you guessed it, the BizFlow Archive Windows Service. Attempting to stop the bizflow service, we were met with the rather unhelpful “Error 1053: The service did not respond to the start or control request in a timely fashion.” This led me to hypothesize that the service was “stuck” processing some rows for some unknown reason.

In order to get the Archive service to actually stop, we had to kill the process using SysInternals Process Monitor. This tool allowed us to identify the service with the particular command-line unique to the Archive service.

Since we couldn’t simply leave the Archiving service turned off; we needed a way to identify the cause of the blocking. I remembered seeing an article by Paul Randal, talking about using DBCC PAGE to inspect the contents of specific pages, and how you could use that to identify actual rows. I found that blog post, on MSDN, and used that information to successfully identify the specific problem row.

This blog post shows how I identified the row. Identifying the row allowed us to correct some inaccurate data that was causing the BizFlow Archive service to stall in the middle of processing. Perhaps if the BizFlow service was not performing row-by-agonizing-row processing, and instead caching the results of whatever SELECT statement it is running we would never have seen this issue in the first place.

The code presented here has been tested using SQL Server 2012. As with all code you see on the Internet, please use this at your own risk, and you probably shouldn’t do this on a production system until you are certain of the impact. DBCC PAGE is an undocumented SQL Server command, and as such the standard limitations apply: the command itself may go away in future (unlikely in this case), the output may change, and the command itself is not officially supported by Microsoft.

First, I’m going to create a sample database, and populate it with some fairly innocuous data, so we have something to work with.

CREATE DATABASE DbccPageDemo;
ALTER DATABASE DbccPageDemo SET RECOVERY SIMPLE;
ALTER DATABASE DbccPageDemo 
MODIFY FILE (NAME=DbccPageDemo, NEWNAME=DbccPageDemo_File);
GO

USE DbccPageDemo;
GO

CREATE TABLE dbo.SomeRows
(
	RowID INT NOT NULL CONSTRAINT PK_SomeRows 
            PRIMARY KEY CLUSTERED IDENTITY(1,1)
	, SomeData VARCHAR(255)
);

/*
   Insert some dummy rows, using the sys.columns catalog view.
*/
INSERT INTO dbo.SomeRows (SomeData)
SELECT c.name + ' ' + c1.name
FROM sys.columns c, sys.columns c1;

On my system, this created a table with 484,416 rows; enough to make the clustered index have great selectivity which avoids locking the entire table during this next part.

In a new window in SQL Server Management Studio, we create an explicit transaction, and update a small percentage of the rows in the table. Note the transaction remains in an open state, since there is no COMMIT TRANS or ROLLBACK TRANS.

BEGIN TRAN 

UPDATE dbo.SomeRows 
SET SomeData = REVERSE(SomeData) 
WHERE SomeData LIKE 'chk %';

In another new window in SQL Server Management Studio, we attempt to select the same rows that are being updated in the prior code, which creates some blocking; imitating the symptoms I was seeing on my client’s Server..


USE DbccPageDemo;

SELECT *
FROM dbo.SomeRows S
WHERE S.SomeData LIKE 'chk%';

In yet another SSMS Window, run the following T-SQL that identifies the database, table, and rows contained on the page involved in the blocking operations.

/*
	T-SQL that identifies rows affected by blocking actions 

	By:		Max Vernon
			http://dba.stackexchange.com/users/10832/max-vernon
	Date:	2015-02-07

	Note:	This only returns rows that are locked at the
			page-granularity level.  Work is needed to 
			return rows that are locked at the row-level.

			This was designed to run on SQL Server 2012 SP2
			and uses the undocumented and unsupported
			DBCC PAGE command to identify rows.

			Use at your own risk on production systems!
*/

SET NOCOUNT ON;

IF NOT(
	SELECT COUNT(*)
	FROM sys.dm_exec_requests der
		INNER JOIN sys.dm_tran_locks dtl ON der.blocking_session_id = dtl.request_session_id 
	WHERE der.blocking_session_id <> 0
		AND dtl.resource_type = 'PAGE'
	) = 0
BEGIN

	DECLARE @DBName SYSNAME;
	DECLARE @ResDesc NVARCHAR(256);
	DECLARE @DatabaseID INT;
	DECLARE @FileNum INT;
	DECLARE @PageNum BIGINT;
	DECLARE @SepPos INT;
	DECLARE @cmd NVARCHAR(2000);
	DECLARE @ObjectName SYSNAME;

	IF (SELECT COALESCE(OBJECT_ID('tempdb..#dbcout'), 0)) = 0
	BEGIN
		CREATE TABLE #dbcout
		(
			  ParentObject VARCHAR(128)
			  , [Object] VARCHAR(128)
			  , [Field] VARCHAR(128)
			  , VALUE VARCHAR(2000)
		);
	END
	ELSE
	BEGIN
		TRUNCATE TABLE #dbcout;
	END

	IF (SELECT COALESCE(OBJECT_ID('tempdb..#dbcrep'), 0)) = 0
	BEGIN
		CREATE TABLE #dbcrep
		(
			  ParentObject VARCHAR(128)
			  , [Object] VARCHAR(128)
			  , [Field] VARCHAR(128)
			  , VALUE VARCHAR(2000)
			  , database_id INT
			  , [file_id] INT
			  , [page_id] BIGINT
			  , ObjectName SYSNAME
		);
	END
	ELSE
	BEGIN
		TRUNCATE TABLE #dbcrep;
	END


	/*
		Declare a cursor so we can run DBCC PAGE on each page currently locked
	*/
	DECLARE cur CURSOR LOCAL FORWARD_ONLY FOR 
	SELECT DBName = DB_NAME(dtl.resource_database_id)
		, ResDesc = dtl.resource_description
		, DatabaseID = dtl.resource_database_id
	FROM sys.dm_exec_requests der
		INNER JOIN sys.dm_tran_locks dtl ON der.blocking_session_id = dtl.request_session_id 
	WHERE der.blocking_session_id <> 0
		AND dtl.resource_type = 'PAGE';

	OPEN cur;

	FETCH NEXT FROM cur
	INTO @DBName, @ResDesc, @DatabaseID;

	WHILE @@FETCH_STATUS = 0
	BEGIN
		SET @SepPos = CHARINDEX(':', @ResDesc, 1);
		SET @FileNum = CONVERT(INT, SUBSTRING(@ResDesc, 1, @SepPos - 1));
		SET @PageNum = CONVERT(BIGINT, SUBSTRING(@ResDesc, @SepPos + 1, LEN(@ResDesc) - @SepPos));
		SET @cmd = 'DBCC PAGE (''' + @DBName + ''',' 
			+ CONVERT(NVARCHAR(16), @FileNum) + ',' 
			+ CONVERT(NVARCHAR(50), @PageNum) 
			+ ',3) WITH TABLERESULTS, NO_INFOMSGS;';
		INSERT INTO #dbcout
		EXEC (@cmd);

		SELECT @ObjectName = o.name
		FROM sys.dm_db_database_page_allocations(@DatabaseID, NULL, NULL, NULL, 'LIMITED') dpa
			INNER JOIN sys.objects o ON dpa.object_id = o.object_id
		WHERE dpa.allocated_page_file_id = @FileNum
			AND dpa.allocated_page_page_id = @PageNum;

		INSERT INTO #dbcrep (ParentObject, Object, Field, VALUE, database_id, file_id, page_id, ObjectName)
		SELECT *, @DatabaseID, @FileNum, @PageNum, @ObjectName
		FROM #dbcout;

		TRUNCATE TABLE #dbcout;

		FETCH NEXT FROM cur
		INTO @DBName, @ResDesc, @DatabaseID;
	END

	/*
		Don't forget to close-and-deallocate cursors when finished with them
	*/
	CLOSE cur;
	DEALLOCATE cur;

	/*
		Compile a dynamic list of column names in the table 
		returned by the DBCC PAGE output
	*/
	DECLARE @FieldList NVARCHAR(2000);
	SET @FieldList = '';
	SELECT @FieldList = @Fieldlist + CASE WHEN @FieldList = '' THEN '' ELSE ', ' END 
		+ 'MAX(CASE WHEN [field] = ''' + Field + ''' THEN [Value] ELSE '''' END) AS ' + Field 
	FROM #dbcrep
	WHERE ParentObject LIKE 'Slot % Offset %' 
		  AND [OBject] LIKE 'Slot % Column %'
		  AND [Field] <> ''
		  AND [Field] <> 'Record Type'
		  AND [Field] <> 'Record Attributes'
	GROUP BY Field;

	DECLARE @query NVARCHAR(MAX);

	SET @Query = N'SELECT DatabaseName = d.name, FileName = mf.name, PageID = dbr.page_id, dbr.ObjectName, ' + @FieldList + N'
	FROM #dbcrep dbr
		INNER JOIN master.sys.databases d ON dbr.database_id = d.database_id
		INNER JOIN master.sys.master_files mf ON dbr.database_id = mf.database_id
			AND dbr.file_id = mf.file_id
	WHERE dbr.ParentObject LIKE ''Slot % Offset %'' 
			AND dbr.[OBject] LIKE ''Slot % Column %''
			AND dbr.[Field] <> ''''
			AND dbr.[Field] <> ''Record Type''
			AND dbr.[Field] <> ''Record Attributes''
	GROUP BY dbr.[ParentObject], d.name, mf.name, dbr.page_id, dbr.ObjectName
	ORDER BY d.name, mf.name, dbr.page_id;';

	/*
		Execute the dynamic query above to display the actual contents
		of rows on the pages identified in the DBCC PAGE command
	*/
	EXEC (@Query);

	/*
		Cleanup the temporary tables
	*/
	DROP TABLE #dbcout;
	DROP TABLE #dbcrep;
END
ELSE
BEGIN
	DECLARE @msg VARCHAR(255);
	SET @msg = 'No pages are being blocked at present.';
	RAISERROR (@msg, 0, 1) WITH NOWAIT;
END

The code above should work for a wide variety of table structures, however be aware there are various limitations, including some fairly significant ones. For instance, if the rows being blocked are part of an index, the DBCC PAGE command returns two rowsets causing the INSERT INTO … EXEC (‘DBCC PAGE…’) command to fail.

My hope is this code will help in at least some situations where it is helpful to identify the rows that are involved in blocking operations.