How to get the sql object name using the Resource Id

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 .