-> Consider you are working on a blocking issue where the application team is advising that the blocking is highly intermittent.
-> Application team is advising that they have a third party monitoring solution and that shows that the blocking is happening on a page and below is the detail,
-> We need to find what Table this Page belongs to. We can utilize DMV sys.dm_db_page_info to get this info. Below query can exactly provide tis,
select DB_NAME(database_id) DBName,page_id,OBJECT_NAME(object_id) TableName from sys.dm_db_page_info (8,1,10029,DEFAULT) --select DB_NAME(database_id) DBName,page_id,OBJECT_NAME(object_id) TableName from sys.dm_db_page_info (DBID,FileID,PAGEID,MODE)
-> Please note that DMV sys.dm_db_page_info is only available in SQL Server 2019 and above.
-> If you are below SQL Server 2019, then you will need to use DBCC PAGE to get the details of table name from Page id. But DBCC PAGE is an undocumented command.
Hope this helps!!!
The views expressed on this blog are mine alone and do not reflect the views of my company or anyone else. All postings on this blog are provided “AS IS” with no warranties, and confers no rights.