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.