A few days ago, one of my customers asked if there is a possibility to get a notification from SQL Server when a query has to wait for a longer time, because an incompatible lock exists on the same object. The answer to this question was easy, because SQL Server provides you for this functionality the so-called Blocked Process Report. Unfortunately this functionality is not enabled by default. Therefore I want to give you a short overview about the Blocked Process Report in this weblog posting, and you can use it to further troubleshoot locking scenarios inside your SQL Server installation.

The Blocked Process Report itself is a simple event that you can trace through SQL Server Profiler or SQL Trace. The event is called Blocked Process Report and you can find it within the event group Errors and Warnings:

But that event is only raised, when you enable the Blocked Process Report functionality on the SQL Server instance level through the sp_configure option blocked process threshold. That option just accepts a number between 0 and 86400 and is the number of seconds that a query must wait for a lock until SQL Server raises the Blocked Process Report event. By default that configuration option has a value of 0 which means that this event is never raised. The following code sets the threshold value to 10 seconds:

sp_configure 'blocked process threshold', 10
RECONFIGURE
GO

To demonstrate the Blocked Process Report, I’m just creating a new transaction inside the AdventureWorks2008R2 database through an UPDATE statement:

BEGIN TRANSACTION

UPDATE Person.Person
SET Title = 'Mr'
WHERE BusinessEntityID = 1

After the execution of this statement, the query has now acquired an Exclusive Lock (X) on the record where the column BusinessEntityID is equal to 1. In a second session I’m now trying to read the same record. During the reading SQL Server tries to acquire a Shared Lock (S) which leads to a blocking scenario:

SELECT * FROM Person.Person
WHERE BusinessEntityID = 1
GO

When you have started SQL Server Profiler and when you have configured the Blocked Process Report event, you will see that the event is reported about after 10 seconds:

As you can see from the screenshot, the Blocked Process Report itself is just XML data, so it is very easy to further analyze it, when you are familiar with XML and XQuery.There are 2 important nodes of XML Data – <blocked-process> and <blocking-process>. The first one – <blocked-process> describes the session that was blocked. In our case this was the second session that issued the SELECT statement against the AdventureWorks2008R2 database. The most important thing here is the XML attribute waitresource, which contains the locked resource on which the session was waiting and exceeding the Blocked Process Threshold configuration option.

The second node <blocking-process> describes the session that currently holds the incompatible lock on the resource, on which the other session wants to acquire the lock. The most important part here is the XML element <inputbuf> which shows the SQL statement that acquired the incompatible lock. With that information in your hand it is very easy to further troubleshoot why the Blocking Threshold was exceeded and how you can continue on that (like killing the other session, when it is an orphaned transaction).

The most important thing that you have to remember when you are working with the Blocked Process Report is the fact that SQL Server just generates that XML report, SQL Server WILL NOT resolve the locking/blocking scenario for you! In our case this means that the second session with the SELECT statement will be also running and waiting after SQL Server has raised the Blocked Process Report – SQL Server will never kill here a session – SQL Server just reports that one session has exceeded the Blocked Process Threshold – nothing more.

Thanks for reading

-Klaus