SQL SERVER – What Resource Wait Are We Seeing?

I have been a big supporter of the Activity Monitor capability inside SQL Server Management Studio. And I have written a number of articles before in this blog. Please take a moment to look at:

SQL SERVER – Activity Monitor to Identify Blocking – Find Expensive Queries

One of the readers wrote to me on this blog and asked me something simple and I was surprised I had not touched on it before. His mail was:

“Hi Pinal,

I have seen your blog around Activity Monitor and as a budding DBA, I am still learning the tricks of the trade. I use many scripts from this site and they have helped me a number of times in my career.

In Activity monitor, I notice a large number of PAGELATCH waits. If I look at processes, I sometimes see a large number of processes waiting for PAGELATCH_SH and PAGELATCH_UP on resources 2:1:3 and sometimes on 2:1:1. This shows contention is placed on SGAM and PFS pages.

Do you have a script written somewhere to find what type of disk resource is under contention? After reading a number of web search I can see this is tempdb contention because of database id of 2.

I am sure you can help me somehow.

Thanks,

Blog reader”

I personally didn’t see much of a problem in his request. And I was not aware if this script existed in this blog. So I went ahead to write the same. This is a query based on the sys.dm_exec_requests, sys.dm_exec_sessions and sys.dm_exec_qury_plan.

SELECT es.session_id, DB_NAME(er.database_id) AS [database_name],
OBJECT_NAME(qp.objectid, qp.dbid) AS [object_name], -- NULL if Ad-Hoc or Prepared statements
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

The output will help you in analyzing the typical wait resource type and if you are having tempdb contention this should show up here. For a matter of fact, if you have other contentions on disk, that would show up too.

Do let me know if this script was helpful and I will be more than happy to extend the same. Or if you extended the script, please make sure to share the same via comments.

Reference : Pinal Dave (https://blog.sqlauthority.com)