18 April,2013 by Jack Vamvas
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