Get Your Own Blog
If you would like to blog on SQLServerCentral.com then send an email to
Contact the author
for this blog
I have total of 11 years of IT experience with Application development, Database Development and Database Administration. I have worked with different version of SQL server from 7.0 to 2008.Started my carrier as VB ,VC++ and database developer in a banking sector for implementing their core banking solution. Currently working as Database Administrator with wide knowledge in performance tuning, high availability solution, troubleshooting and server monitoring. This blog is my humble attempt to share my knowledge and what I learned from my day to day work.
12 April 2012
When I was working with couple of frequent blocking issue in our live server, I have noticed different types of wait resources which are not meaningful unless they mapped to a table or an index. In this post I will explain how to map the wait resources to table/index.
Key Wait Resource
Below is a sample of blocked process report with Key as wait resource .
The first part of the key wait resource is the database id and second part is called as Hobt_Id. Hobt is an acronym for Heap Or B Tree. The hobt_id can be mapped to sys.indexes and sys.objects through sys.partitions. Below script will map the key wait resource to corresponding index and table.
o.name AS TableName,
i.name AS IndexName,
SCHEMA_NAME(o.schema_id) AS SchemaName
FROM sys.partitions p JOIN sys.objects o ON p.OBJECT_ID = o.OBJECT_ID
JOIN sys.indexes i ON p.OBJECT_ID = i.OBJECT_ID AND p.index_id = i.index_id
WHERE p.hobt_id = 72057594040811520
Page Wait Resource
Below is a sample of blocked process report with Page as wait resource .
As everyone knows, the first part of the wait resource is the database id (68) , second part is file id (1) and third part is the page number(492478). To find out the object id associated with this page follow the below steps.
Select the database based on the output above statement. Then execute the below statement
DBCC traceon (3604)
DBCC page (68, 1, 492478) --Database_id,file_id,page_id
This will give a result as given below.
The object id can be mapped to a table using the system function object_name()
Object Wait Resource
Below is a sample of blocked process report with Object as wait resource .
Here also the first part of the wait resource is the database id(68) ,second part is the object id and third part is known as lock partition id. The object id part can be mapped to an object using the object_name() system function . The lock partition id is not very useful in troubleshooting the blocking issue.This will have positive value only when the server has more than 16 CPU. I will explain more about the lock partition in my future post.
Leave a comment on the original post
[www.practicalsqldba.com, opens in a new window]