Dissecting Blocked-process-Report in SQL Server

Objective:
This post explains How to analyze the blocked-process-report captured using Event Notification in detail,However, this does not cover the configuration/set up of the Event notification in SQL Server.

Let us look at a typical report captured. The report has been logically divided into three sections to make our analysis simple.

First Section:
BPR_Section1

The above image shows the first section which contains
EventType – Event type of the Report which is BLOCKED_PROCESS_REPORT
PostTime – It is the date and time that the event posts
SPID – Session ID(Server Process ID) information

Second Section:
BlockedProcess

Second section contains two major tags – Blocked-process and Blocking-process.

In Blocked Process, we can first look at the waitresource attribute. Here in the above image waitresource=”KEY: 160:72057595494989824 (09dec3797d4c)”.


KEY- represents the lock on the object is at Key level

160- The second section is the database id.

		To get the database name, you can use the below query:
		Select DB_NAME(160)--DBNAME

72057595494989824- This section can be used to identify the associated object with the blocking as below.
				
		Select OBJECT_NAME(object_id) From sys.partitions where hobt_id =72057595494989824


(09dec3797d4c)	- This represents the actual data. This can be queried on the associated objects using %%lockres%% 

		Select * From <> Where %%lockres%% = '(77576a23d4db)'


Note: %%lockres%% is an undocumented virtual column to identify the key hash value. As this is an undocumented feature, Microsoft will no longer support its usage in production environment, hence no warranties.

From the above, we would be able to get the type of lock,Databasename, the associated object and data.

Further, let us look at the inputbuf tag.


Proc [Database Id = 160 Object Id = 2114374238]
Proc [Database Id = 160 Object Id = 1354487904] 

- This section provides the query/procedure information.
Select * From sys.procedures where object_id=2114374238 
Select * From sys.procedures where object_id=1354487904 

Third Section:

BPR_Section3

Finally, the third section includes other basic information like DatabaseID, TransactionID, Duration, StartTime, EndTime, ObjectID, IndexID, ServerName, Mode, LoginSID, EventSequence, IsSystem and SessionLoginName.

Hope, this post will help you to identify some important information from the blocked process report that can be used for troubleshooting your blocking/locking issues.

Have your thoughts on the same as part of our learning an sharing....Have a good day ahead!

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s