A guide to Understand Key wait resource in Blocking

14 December,2012 by Tom Collins

During SQL blocking , creating a Blocked Process Report is very useful in reporting Blocking details. When you’re interpreting the Blocked Process Report – it is important to understand what resources are blocked.

Reading through the Report details , the waitresource indicates the resource needed by the task.  Let’s break down the details to understand the resource.

KEY: db_id:hobt_id (index key hash value).

KEY Identifies the key range within an index on which a lock is held or requested. In our example : KEY:9:330708921417728


process id="processb853d8" taskpriority="0" logused="0" waitresource="KEY:9:330708921417728 (03000d8f0ecc)" waittime="572343" ownerId="602608592" transactionname="SELECT" lasttranstarted="2012-12-14T09:46:57.550" XDES="0x267d67c8" lockMode="S" schedulerid="4" kpid="5688" status="suspended" spid="81" sbid="0" ecid="0" priority="0" transcount="0" lastbatchstarted="2012-12-14T09:46:57.550" lastbatchcompleted="2012-12-14T09:46:57.550" lastattention="2012-12-14T09:46:16.757" clientapp="MyApp" hostname="SERVER1" hostpid="1000" loginname="A_login_name" isolationlevel="read committed (2)" xactid="602608592" currentdb="9" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128568"

 Mapping  the hobt_id to sys.indexes and sys.objects with sys.partitions will return the Table Name and Index Name . This query returns the Table Name, Index Name and Schema Name.

obj.name AS Table_Name, 
ind.name AS Index_Name,
SCHEMA_NAME(obj.schema_id) AS Schema_name
FROM sys.partitions par JOIN sys.objects obj ON par.OBJECT_ID = obj.OBJECT_ID 
JOIN sys.indexes ind ON par.OBJECT_ID = ind.OBJECT_ID  AND par.index_id = ind.index_id 
WHERE par.hobt_id = 330708921417728


Read More on monitoring blocked processes

How to monitor Blocked Processes with SQL Alert and email sp_whoisActive report

SQL Server - How to detect and troubleshoot blocking and deadlocks

SQL Server - How to find Open Transactions

SQL Server - How to improve Execution Plan Reuse

SQL Server – How to troubleshoot a slow running query

Author: Tom Collins (http://www.sqlserver-dba.com)


Verify your Comment

Previewing your Comment

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

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.


Post a comment on A guide to Understand Key wait resource in Blocking

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