Easily Identify Object Name from Wait Resource(x:y:z)

New function in SQL 2019

You run a query to check for locking or blocking in SSMS and find a wait resource in the format (8:1:3610). To identify the object name, you would typically run multiple queries, first find database name, turn on trace flag 3604, then find object id from DBCC PAGE and then find object name from sys.objects.
However, with a new function “sys.dm_db_page_info” introduced in SQL Server 2019, you no longer need to go through these steps. Instead, you can run a single query to get the object name directly.

For example, if the wait resource is (8:1:3610)

/* For wait resource (8:1:3610) --> (dbid:fileid:pageid) */

DECLARE @db_id INT = 8         --database id 
DECLARE @file_id INT = 1        --file id
DECLARE @page_id INT = 3610     --page id

SELECT 
   DB_NAME(@db_id) AS [database_name], 
   OBJECT_NAME(page_info.[object_id], @db_id) AS [object_name],
   page_info.page_type_desc
FROM sys.dm_db_page_info(@db_id, @file_id, @page_id, 'DETAILED') AS page_info;

In this case, object name is Badges on StackOverflow2013 database.

• This query works in SQL Server 2019 + versions

• The login executing this query requires following permissions:

  USE [master]
  GO
  GRANT VIEW SERVER PERFORMANCE STATE to [sqluser];
  GO
  
  USE [StackOverflow2013]
  GO
  CREATE USER [sqluser] FOR LOGIN [sqluser]
  GO
  ALTER ROLE [db_datareader] ADD MEMBER [sqluser]
  GO

Additional reading

• https://gohigh.substack.com/p/2-new-functions-in-sql-2019

• https://learn.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-db-page-info-transact-sql?view=sql-server-ver16

• https://learn.microsoft.com/en-us/sql/relational-databases/system-functions/sys-fn-pagerescracker-transact-sql?view=sql-server-ver16