Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Klaus Aschenbrenner

Klaus Aschenbrenner provides independent SQL Server Consulting Services across Europe and the US. Klaus works with the .NET Framework and especially with the SQL Server 2005/2008 from the very beginnings. In the years 2004 - 2005 Klaus was entitled with the MVP award from Microsoft for his tremendous support in the .NET Community. Klaus has also written the book Pro SQL Server 2008 Service Broker which was published by Apress in the Summer of 2008. Further information about Klaus you can find on his homepage at http://www.SQLpassion.at. He also twitters at http://twitter.com/Aschenbrenner.

The Blocked Process Report

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

Comments

Posted by Jason Brimhall on 1 December 2011

I have found this option to be useful in event notifications.  Enable the Blocked Process threshold and setup an event notification and you can store this xml without running profiler.  Very handy!!

Posted by Bob Barrows on 6 December 2011

Could you provide a few more details about this event notification, Jason?

Leave a Comment

Please register or log in to leave a comment.