Troubleshooting - Waits and Queues query
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:-';
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
) AS Requests
FOR [status] IN (sleeping, background,runnable,running )
) AS PivotRequests
ORDER BY mWaitIime DESC