A guide to Understand Key wait resource in Blocking

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.

SELECT 
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