Query to check TempDB contention

[Parth Shinojiya]

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