As a budding DBA, it’s important to learn how to identify and resolve performance issues in SQL Server. One useful tool for monitoring and troubleshooting is the Activity Monitor capability inside SQL Server Management Studio. In a previous article, we discussed how to use the Activity Monitor to identify blocking and find expensive queries.
Recently, a reader reached out to me with a question about the Activity Monitor. They noticed a large number of PAGELATCH waits and wanted to know if there was a way to determine the type of disk resource causing the contention. This is a common issue, especially when it comes to tempdb contention.
To address this question, I have written a script that utilizes the sys.dm_exec_requests, sys.dm_exec_sessions, and sys.dm_exec_query_plan views to identify the wait resource type. Here is the script:
SELECT es.session_id,
DB_NAME(er.database_id) AS [database_name],
OBJECT_NAME(qp.objectid, qp.dbid) AS [object_name],
er.wait_type,
er.wait_resource,
er.status,
(SELECT CASE
WHEN pageid = 1 OR pageid % 8088 = 0 THEN 'Is_PFS_Page'
WHEN pageid = 2 OR pageid % 511232 = 0 THEN 'Is_GAM_Page'
WHEN pageid = 3 OR (pageid - 1) % 511232 = 0 THEN 'Is_SGAM_Page'
WHEN pageid IS NULL THEN NULL
ELSE 'Is Not PFS, GAM or SGAM page'
END
FROM (SELECT CASE
WHEN er.[wait_type] LIKE 'PAGE%LATCH%' AND er.[wait_resource] LIKE '%:%' THEN CAST(RIGHT(er.[wait_resource], LEN(er.[wait_resource]) - CHARINDEX(':', er.[wait_resource], LEN(er.[wait_resource]) - CHARINDEX(':', REVERSE(er.[wait_resource])))) AS INT)
ELSE NULL
END AS pageid) AS latch_pageid) AS wait_resource_type,
er.wait_time AS wait_time_ms,
(SELECT qt.TEXT AS [text()]
FROM sys.dm_exec_sql_text(er.sql_handle) AS qt
FOR XML PATH(''), TYPE) AS [running_batch],
(SELECT SUBSTRING(qt2.TEXT, (CASE
WHEN er.statement_start_offset = 0 THEN 0
ELSE er.statement_start_offset / 2
END), (CASE
WHEN er.statement_end_offset = -1 THEN DATALENGTH(qt2.TEXT)
ELSE er.statement_end_offset / 2
END - (CASE
WHEN er.statement_start_offset = 0 THEN 0
ELSE er.statement_start_offset / 2
END))) AS [text()]
FROM sys.dm_exec_sql_text(er.sql_handle) AS qt2
FOR XML PATH(''), TYPE) AS [running_statement],
qp.query_plan
FROM sys.dm_exec_requests er
LEFT OUTER JOIN sys.dm_exec_sessions es ON er.session_id = es.session_id
CROSS APPLY sys.dm_exec_query_plan(er.plan_handle) qp
WHERE er.session_id <> @@SPID
AND es.is_user_process = 1
ORDER BY er.total_elapsed_time DESC,
er.logical_reads DESC,
[database_name],
session_id
This script will provide you with valuable information about the wait resource type, allowing you to analyze and troubleshoot contention issues. If you are experiencing tempdb contention, it should be evident in the output. Additionally, if you have other disk contentions, they will also be visible.
I hope this script proves helpful in your journey as a DBA. If you have any further questions or if you have extended the script, please feel free to share your thoughts in the comments section below. Happy troubleshooting!