Identifying Objects Based on Page IDs

Identifying Objects Based on Page IDs

May 11, 2016

You may have seen situations when you query sys.dm_exec_requests and find PAGEIOLATCH_*, PAGELATCH_* wait types on the wait_type column and corresponding resource on which the lock is held on the  wait_resource column. The wait resource is typically a page written in the format DB ID:FILE ID:PAGE ID. To identify the page associated with this page you can use the following:

dbcc traceon (3604)
dbcc page(5,1,16118795,3)
select DB_NAME(5)

USE <output of above>
select OBJECT_NAME (82099333)

  • Trace 3604 enable SQL Server to print the output of the DBCC PAGE command on the messages portion of the output pane.
  • DBCC PAGE takes four parameters – the Database ID, File ID, Page ID and Output Level. I typically use 3 for detail.
  • The OBJECT_NAME function takes a parameter OBJECT_ID which is displayed on the output of DBCC PAGE similar to the below:

Metadata: ObjectId = 82099333        m_prevPage = (6:1110436)             m_nextPage = (1:16127354)

 

Igiri Books © Copyright 2016. All Rights Reserved. Site Credits: AppWorld