2 new functions in SQL 2019
fn_PageResCracker and sys.dm_db_page_info
SQL Server 2019 introduced two new functions for troubleshooting page-related waits and blocking: fn_PageResCracker and sys.dm_db_page_info.
fn_PageResCracker provides details about the
page_id,file_id, anddatabase_id.sys.dm_db_page_info provides details about the
object_idandindex_id. It is a replacement forDBCC PAGEcommand.
These 2 functions when used in conjunction provides details about what object the page belongs to and what page type it is. This helps to troubleshoot page related waits and blocking in SQL Server.
Example Query
Let’s take a look at how to use fn_PageResCracker and sys.dm_db_page_info in SQL Server 2019 and above to troubleshoot page related waits.
In the query below, fn_PageResCracker and sys.dm_db_page_info are used in conjunction with sys.dm_exec_requests to retrieve a list of active requests and the pages those processes are trying to access:
SELECT page_info.*
FROM sys.dm_exec_requests AS d
CROSS APPLY sys.fn_PageResCracker(d.page_resource) AS r
CROSS APPLY sys.dm_db_page_info(r.db_id, r.file_id, r.page_id, 'DETAILED') AS page_info;In the screenshot below, it tells you what page_type the page number is and you don’t have to do any calculations if that is a PFS, GAM or SGAM page.
If you need to check TempDB Contention, you can use the following query with 2 functions:
/*
SCRIPT TO CHECK CONTENTION ON SQL2019 AND ABOVE
https://www.microsoft.com/en-us/sql-server/blog/2022/07/21/improve-scalability-with-system-page-latch-concurrency-enhancements-in-sql-server-2022/
*/
USE master
GO
SELECT
er.session_id, er.wait_type, er.wait_resource,
OBJECT_NAME(page_info.[object_id],page_info.database_id) as [object_name],
page_info.page_type_desc,
er.blocking_session_id,er.command,
SUBSTRING(st.text, (er.statement_start_offset/2)+1,
((CASE er.statement_end_offset
WHEN -1 THEN DATALENGTH(st.text)
ELSE er.statement_end_offset
END - er.statement_start_offset)/2) + 1) AS statement_text,
page_info.database_id,page_info.[file_id], page_info.page_id, page_info.[object_id],
page_info.index_id,
CASE
WHEN page_info.page_type_desc IN('SGAM_PAGE','GAM_PAGE', 'PFS_PAGE')
AND WAIT_TYPE IN ('PAGELATCH_SH','PAGELATCH_UP','PAGELATCH_EX')THEN 'ALLOCATION CONTENTION'
WHEN page_info.page_type_desc IN ('DATA_PAGE', 'INDEX_PAGE')
AND WAIT_TYPE IN ('PAGELATCH_SH','PAGELATCH_UP','PAGELATCH_EX')THEN 'METADATA CONTENTION'
END AS allocation_type
FROM sys.dm_exec_requests AS er
CROSS APPLY sys.dm_exec_sql_text(er.sql_handle) AS st
CROSS APPLY sys.fn_PageResCracker (er.page_resource) AS r --database ID, file ID, page ID SQL2019
CROSS APPLY sys.dm_db_page_info(r.[db_id], r.[file_id], r.page_id, 'DETAILED') AS page_info --replace dbcc page SQL2019
WHERE er.wait_type like 'PAGELATCH%'
GO
Conclusion
By using fn_PageResCracker and sys.dm_db_page_info in your queries, you can get information on which pages and objects are being used and waited on, especially when troubleshooting performance issues related to page contention in SQL Server 2019 and above.