Subscribe

SQLServer-DBA.com Links

Subscribe to newsletter

Dba_db2_button

Powered by TypePad
SQL Server – Understand Key wait resource in Blocking

14 December,2012 by Jack Vamvas

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

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: Jack Vamvas (http://www.sqlserver-dba.com)

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

Subscribe to SQLServer-DBA.com

Latest posts delivered to you daily

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