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)
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)