How to find Table Name from Page ID in SQL Server

Recently, we had a performance issue in one of our testing environment and while we are troubleshooting the issue with Live Monitoring Query, we identified a locking scenario which is the root cause for the performance problem.

Now, we know there is a locking and the wait resource is showing as PAGE: 9:1:401776. What does it mean? How do we know which object is being locked? Let us try to answer these questions.

We know there is a LCK_M_IX which means an intent exclusive modification lock happened on the object “PAGE: 9:1:401776”. If we have a page number like this, then there is an easy way to get the object information with the help of a DBCC command – DBCC PAGE.

DBCC TRACEON (3604);
DBCC PAGE (9, 1, 401776, 0);
DBCC TRACEOFF (3604);

The result of DBCC PAGE provides lots of information, however, we should specifically look at PAGE HEADER section for “Metadata: ObjectId “. It would provide us the ObjectId. Once we have the object_id, it is an easy to get the object name as below. Please note, we should select the database name while executing the command otherwise, we might end up no result or wrong information.

Select db_name(9)
GO
use <dbname_from_previous_query>
GO
Select object_name(946818435)

I’d like to grow my readership. If you enjoyed this blog post, please share it with your friends!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s