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:
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:
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:
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!
SQLZealots 
it would be grate if you post a blog on how to convert this xml data into tabular data
LikeLike