SQL SERVER – What Resource Wait Are We Seeing?

Which resource wait is Activity Monitor really showing? 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.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.