Wait stat RRS feed

  • Question

  • I am trying to troubleshoot the server performance..

    I am trying to look at the wait stat.. and trying to figure out what is acutally waiting..

    wait time is constantly 700~ 800 ms...

    how can I wuery to find out what is actually waiting?

    Friday, February 10, 2012 10:05 PM


  • This query will help you

    WITH Waits AS
    (SELECT wait_type, wait_time_ms / 1000. AS wait_time_s,
    100. * wait_time_ms / SUM(wait_time_ms) OVER() AS pct,
    ROW_NUMBER() OVER(ORDER BY wait_time_ms DESC) AS rn
    FROM sys.dm_os_wait_stats
    SELECT W1.wait_type,
    CAST(W1.wait_time_s AS DECIMAL(12, 2)) AS wait_time_s,
    CAST(W1.pct AS DECIMAL(12, 2)) AS pct,
    CAST(SUM(W2.pct) AS DECIMAL(12, 2)) AS running_pct
    FROM Waits AS W1
    INNER JOIN Waits AS W2
    ON W2.rn <= W1.rn
    GROUP BY W1.rn, W1.wait_type, W1.wait_time_s, W1.pct
    HAVING SUM(W2.pct) - W1.pct < 99 OPTION (RECOMPILE);

    Please mark answered if I've answered your question and vote for it as helpful to help other user's find a solution quicker

    • Proposed as answer by Peja Tao Tuesday, February 14, 2012 5:33 AM
    • Marked as answer by Peja Tao Monday, February 20, 2012 1:35 AM
    Friday, February 10, 2012 10:07 PM