Getting Started with Adobe After Effects - Part 6: Motion Blur


Upload Image Close it
Select File

This blog about the practical solutions for the problems that SQL server DBA face in their day to day work
Browse by Tags · View All
Internals 12
Index 12
Implimentation 6
Administration 6
Security 6
Failover cluster 6
Troubleshooting 5
Index Fragmentation 3
SSRS 2
Alerts 2

Archive · View All
September 2012 10
March 2013 9
August 2012 9
March 2012 9
July 2012 8
October 2012 5
June 2012 4
April 2012 4
April 2013 3
February 2012 2

PracticalSqlDba

SQL server: Deciphering Wait resource

Apr 12 2012 12:00AM by Nelson John A   

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.

SELECT 
 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 DB_NAME(68)
Select the database based on the output above statement. Then execute the below statement
DBCC traceon (3604)
GO
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.

If you liked this post, do like my page on FaceBook at http://www.facebook.com/practicalSqlDba

















Republished from Practical SQL DBA [24 clicks].  Read the original version here [1 clicks].
Nelson John A
478 · 0% · 78
2
 
0
Lifesaver
 
0
Refreshed
 
0
Learned
 
0
Incorrect


Submit

Your Comment
Sign Up or Login to post a comment.
"SQL server: Deciphering Wait resource" rated 5 out of 5 by 2 readers
SQL server: Deciphering Wait resource , 5.0 out of 5 based on 2 ratings
    Copyright © Rivera Informatic Private Ltd Contact us      Privacy Policy      Terms of use      Report Abuse      Advertising      [ZULU1097]