How to get the sql object name using the Resource Id

23 February,2016 by Jack Vamvas

When doing some SQL Server performance troubleshooting I was using the sys.dm_os_waiting_tasks DMV to identify what tasks were waiting on resources. One of the columns available is resource description. The resource description column is the description of the resource that is being consumed.

Typically you will see something like this resource id  :

24:1:13345565

How can you find out the object name being referenced?

The pattern of the numerical description is : <db-id>:<file-id>:<page-in-file>

First we can try obtaining the database name with the db-id

select db_name(24)

This gives us the database name.

Next we can identify the file name with the file-id

select FILE_NAME(1)


The problem is it doesn't quite give us the exact information required.  To get the extra piece of information we'll need to use the DBCC PAGE function.

For more details on DBCC PAGE   read 3 ways to have fun with DBCC PAGE - SQL Server DBA

To view the output you'll need to enable the trace 3604.
Then use the information from the output.


DBCC TRACEON (3604) -- To enable trace 3604 for this session
DBCC PAGE(24:1:13345565)

This image is an example of the output. The highlighted title is : Metadata ObjectId = 99

Use a query such as :

SELECT OBJECT_NAME(object_id)

 

To return the object name. Once you have the object name , you can drill down into the root causes .

ObjectId

 

 


Author: Jack Vamvas (http://www.sqlserver-dba.com)


Share:

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 on How to get the sql object name using the Resource Id


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