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

Page, yes.  DBCC Page?  Not so much.

    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:

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.

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.

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

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.

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.

Ads by Google, Paying the Rent: