Troubleshooting - Waits and Queues query
By Vic
Saturday, March 9, 2019

Troubleshooting - Waits and Queues query

sys.dm_exec_requests is a key troubleshooting data management view. Using PIVOT this view can present the number of requests in each queue status and if SUSPENDED the reason for the wait. 

 The text for the above query is available at the bottom of this post. 

Where is this query useful and what does it tell me?

A server has been reported as running slow. You need to under the problem. The above query will help you at a high level determine if the server is under load pressure or there is resource contention. If the query returns a high number of requests in the RUNNABLE column, the server has a large number of request queuing i.e. waiting for a scheduler. Requests in the SUSPENDED column will be broken down by wait type. 

SELECT [Description] = 'Request Status By Wait Type:-';

SELECT
wait_type,
mWaitIime,
[waittimePercentage],
sleeping,
background,
runnable,
running
FROM
(
SELECT
[status],
wait_type,
mWaitIime = MAX(wait_time),
100.0 * SUM(CONVERT(float,wait_time)) / SUM(SUM (CASE CONVERT(float,wait_time) WHEN 0 THEN 1 ELSE CONVERT(float,wait_time) END)) OVER() AS [waittimePercentage],
COUNT(1) AS Requests
FROM sys.dm_exec_requests
GROUP BY
[status],
wait_type
) AS Requests
PIVOT
(
SUM(Requests)
FOR [status] IN (sleeping, background,runnable,running )
) AS PivotRequests
ORDER BY mWaitIime DESC