Blocked Process Report and how to read

The Blocked Process Report is an invaluable tool available in SQL Server Profiler.  Creating the Report is easy , read this post for setting up a Blocked Process Report SQL Server – Find queries causing Lock Timeouts per second

Interpreting the Blocked Process report requires some extra understanding . Here are some basic usage notes

1)     The Blocked Process Report is  XML Data

2)     I usually use Blocked Process Report for troubleshooting blocking . The performance impact of running the Blocked Process Report is minimal , using the deadlock monitor background thread

3)       Important XML  nodes on the Blocked Process report are blocking-process and blocked-process

4)       The Blocking-process node reports on the session holding the current incompatible lock.

5)       Within the blocking-process node the inputbuf node displays the SQL code with the acquired incompatible lock.

6)       Within the blocking-process node the inputbuf node displays the code waiting to acquire the lock

7)       The Blocked Process Report reports on the blocking but doesn’t resolve the blocking! Use the information to make decisions about next actions – such as KILL and  analysis

8)       Waitresource column is important – found on the blocked-process  node.  The waitresource node represents the resource needed (but currently blocked).  The information is only useful if you can map to a  table\index\key .

An example is  

waitresource="KEY: 10:72057594038321152 (71010ff1d3ee)"

Use the code on   Understand Key wait resource in Blocking - SQL Server DBA to find the name of resource.

 9) Most (but not all) issues related to blocking are non-optimized queries.   Analysing the Execution Plans of the blocking queries , is a quick  method of identifying bottlenecks

Read More

SQL Server – Find queries causing Lock Timeouts per second

Understand Key wait resource in Blocking - SQL Server DBA

What is SQL Server Query Tuning? - SQL Server DBA