Query to check TempDB contention
SQL 2019 and above
Use the query here to check for contention on TempDB.
Run this on SQL Server 2019 and above, since sys.fn_PageResCracker and sys.dm_db_page_info were introduced in SQL 2019.
These 2 functions give detailed information on pages which help to troubleshoot page related waits.
In this case, we are only interested in PAGELATCH, since that is the wait on pages on TempDB leading to contention.
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],
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, page_info.page_type_desc,
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
Interpreting the Results
Once you run the query, scroll to the far right to check the allocation_type column. This will tell you whether it is allocation or metadata contention.
Resolving Allocation Contention
Allocation Contention: Occurs when there is contention on PFS, GAM or SGAM pages. This often happens when there are a large number of concurrent operations that need to acquire exclusive latches on these pages. Resolve as follows:
In case of SQL 2019 and older, add 4 or 8 additional datafiles of same size equal to existing file size and check for contention and repeat.
In case of SQL 2022, this contention will not be seen, it is resolved by default.
Read here for step by step guide to resolve this - https://gohigh.substack.com/p/when-to-add-more-datafiles
Resolving Metadata Contention
Metadata Contention: Occurs when there is contention on system object pages due to high volume of DDL operations. Resolve as follows:
Cache temporary objects
Enable in-memory tempdb metadata feature in SQL 2022
Read here for step by step guide to resolve - https://gohigh.substack.com/p/metadata-contention-on-tempdb
--Query for older versions
USE master;
GO
SELECT
er.session_id,
er.wait_type,
er.wait_resource,
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,
er.database_id,
r.[file_id],
r.page_id,
CASE
WHEN r.page_id % 8088 = 1 THEN 'PFS_PAGE'
WHEN r.page_id % 511232 = 2 THEN 'GAM_PAGE'
WHEN r.page_id % 511232 = 3 THEN 'SGAM_PAGE'
ELSE 'DATA_PAGE/INDEX_PAGE'
END AS page_type_desc,
CASE
WHEN r.page_id % 8088 = 1 THEN 'PFS Contention'
WHEN r.page_id % 511232 = 2 THEN 'GAM Contention'
WHEN r.page_id % 511232 = 3 THEN 'SGAM Contention'
ELSE 'Metadata Contention'
END AS allocation_type
FROM sys.dm_exec_requests AS er
JOIN sys.dm_os_waiting_tasks AS wt
ON er.session_id = wt.session_id
CROSS APPLY sys.dm_exec_sql_text(er.sql_handle) AS st
CROSS APPLY (
SELECT
SUBSTRING(wait_resource, CHARINDEX(':', wait_resource) + 1, LEN(wait_resource)) AS wait_details,
CAST(PARSENAME(REPLACE(wait_resource, ':', '.'), 3) AS INT) AS [file_id],
CAST(PARSENAME(REPLACE(wait_resource, ':', '.'), 2) AS INT) AS page_id
) AS r
WHERE er.wait_type LIKE 'PAGELATCH%'
AND er.database_id = 2 -- TempDB
ORDER BY er.wait_type DESC, er.session_id;
GO