Certain DMV queries that return various statistics that increment since boot would be useful as perfmon counters.

For example, take:

SELECT wait_type, wait_time_ms FROM sys.dm_os_wait_stats;

Most monitoring tools know how to take an incrementing number and turn it into a rate since this is how SNMP counters work. Is anyone aware of any preexisting tools that turn these sort of stats into DMV queries?

2 Answers 2


This doesn't exactly answer your question, but Paul Randal included a very helpful query for aggregating wait stats with one of his surveys last December. I have shamelessly copied and pasted it here, but am providing a link to his site, because there is a wealth of knowledge to be had from it. Wait Stats

        wait_time_ms / 1000.0 AS WaitS,
        (wait_time_ms - signal_wait_time_ms) / 1000.0 AS ResourceS,
        signal_wait_time_ms / 1000.0 AS SignalS,
        waiting_tasks_count AS WaitCount,
        100.0 * wait_time_ms / SUM (wait_time_ms) OVER() AS Percentage,
        ROW_NUMBER() OVER(ORDER BY wait_time_ms DESC) AS RowNum
    FROM sys.dm_os_wait_stats
    WHERE wait_type NOT IN (
    W1.wait_type AS WaitType,
    CAST (W1.WaitS AS DECIMAL(14, 2)) AS Wait_S,
    CAST (W1.ResourceS AS DECIMAL(14, 2)) AS Resource_S,
    CAST (W1.SignalS AS DECIMAL(14, 2)) AS Signal_S,
    W1.WaitCount AS WaitCount,
    CAST (W1.Percentage AS DECIMAL(4, 2)) AS Percentage,
    CAST ((W1.WaitS / W1.WaitCount) AS DECIMAL (14, 4)) AS AvgWait_S,
    CAST ((W1.ResourceS / W1.WaitCount) AS DECIMAL (14, 4)) AS AvgRes_S,
    CAST ((W1.SignalS / W1.WaitCount) AS DECIMAL (14, 4)) AS AvgSig_S
FROM Waits AS W1
    INNER JOIN Waits AS W2 ON W2.RowNum <= W1.RowNum
GROUP BY W1.RowNum, W1.wait_type, W1.WaitS, W1.ResourceS, W1.SignalS, W1.WaitCount, W1.Percentage
HAVING SUM (W2.Percentage) - W1.Percentage < 95; -- percentage threshold

I am not sure if this is what you mean but DbVisualizer does a very nice job displaying this kind of data in a graph. Look for the monitoring feature. What is does is collect data from a query in a grid and you can display the grid as a graph. In the grid you can address previous cells and use them to calculate a difference. Doing so allows you to show the time difference and the increment, effectively giving you a rate. It is multi platform and multi database in a very nice way.

  • I like DBTuna for similar purposes.
    – Gaius
    Commented Mar 29, 2011 at 12:40

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.