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

2015-03-03 · by Hannah Vernon · in Internals

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. We determined that a service related to BizFlow was causing blocking. This post shows how we identified the cause of the issue.

A different kind of Page

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 were 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

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;

CREATE DATABASE DbccPageDemo;

ALTER DATABASE DbccPageDemo SET RECOVERY SIMPLE;

ALTER DATABASE DbccPageDemo

MODIFY FILE (NAME=DbccPageDemo, NEWNAME=DbccPageDemo_File);

USE DbccPageDemo;

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 %';

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%';

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:     Hannah Vernon
            http://dba.stackexchange.com/users/10832/
    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

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

145

146

147

148

149

150

151

152

153

154

155

156

157

158

159

160

161

162

163

164

165

166

167

168

169

170

171

172

173

174

T-SQL that identifies rows affected by blocking actions

By: Hannah Vernon

http://dba.stackexchange.com/users/10832/

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.

This post is part of our series on SQL Server Internals.

Tags: blocking, dbcc

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

Related

2 Responses

Leave a ReplyCancel reply

Search

Categories

Pages

Meta