Identify SQL Server Object Using Resource Page ID

19 Jun
June 19, 2012

Sometimes one might like to identify a particular SQL Server resource using the Page ID this resource is under, you might notice a Page ID sometimes on the Activity Monitor page under the Wait Resource column, if a certain query is waiting on a particular resource, some DMVs will reference Page IDs as well as the SQL Server log files, I find it handy to be able to see exactly what SQL Server Object falls under this Page ID, in order to diagnose any issues with access to that particular resource.

A Page ID looks something like this:
Database-ID:File-ID:Page-ID
For example:
6:2:1358463

This already gives us some information about the physical and logical locations of the object within SQL Server, so in order to retrieve the database name the object falls under, all we have to do is run the following function:

SELECT DB_NAME(Database-ID)

Replacing the Database-ID with the first name in the Resource Page ID.

And in order to get the logical name of the file this Page ID falls under, we can call the following SQL Server function:

SELECT FILE_NAME(File-ID)

Replacing the File-ID with the second name in the Resource Page ID.

Now this is close enough, but we still need to retrieve Object ID, which will actually tell us something a bit more meaningful, and alot less broad than the database or the file this resource is sitting under, for that we can use the following SQL Server function:

DBCC TRACEON (3604) -- To enable trace 3604 for this session
DBCC PAGE(Database-ID, File-ID, Page-ID)

The DBCC PAGE function will return a bunch of information about the page itself, its allocation status, and other interesting information that relates this page to SQL’s objects and system, what we are interested in is the “Metadata: ObjectId” entry, which can be seen below in the image output of the DBCC PAGE command.

After getting the ObjectID, all we need to do is plug it into the following SQL Server function and we have our object name:

SELECT OBJECT_NAME(ObjectID)

And we are cooking!

It is a very handy way of exploring which objects are being troublesome, and digging deeper into SQL’s internals.

Identify SQL Server Object Using Resource Page ID rated 4 out of 5 by 1 readers

Identify SQL Server Object Using Resource Page ID , 4.0 out of 5 based on 1 ratings

* * * *   3 votes
2 replies
  1. Koce says:

    Thanks

    Reply
  2. Maxwell says:

    We’re a group of volunteers and starting a new scheme in our community. Your site offered us with valuable info to work on. You’ve done a formidable process and our entire group will probably be thankful to you.

    Reply

Leave a Reply

Want to join the discussion?
Feel free to contribute!

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>