Sqlserver-dba.com

Subscribe

SQLServer-DBA.com Links

Subscribe to newsletter

Dba_db2_button

Powered by TypePad
Blocked Process Report and how to read

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

 

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


Author: Jack Vamvas (http://www.sqlserver-dba.com)

******** *******

Enjoy this post? Enter your email address for updates on new posts:

Delivered by FeedBurner

Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Working...
Your comment could not be posted. Error type:
Your comment has been posted. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.

Working...

Post a comment


sqlserver-dba.com | SQL Server Performance Tuning | SQL Server DBA:Everything | FAQ | Contact|Copyright & Disclaimer