Wednesday, January 26, 2011

How to use performance counters in sys.dm_os_performance_counters DMV

As you probably already know,
in DMV called 'sys.dm_os_performance_counters' you can find
many performance counters related to the SQL Server.
You can use them instead of using the counters from Windows
Performance Monitor application.

But there is a catch you should be aware of.

There are 3 types of the counters and a counter`s value
calculation depends on the type of the counter.

Counters types are:

In order to get the right value of this kind of counter you have
to take counter with cntr_type = 537003264 and divide it by
'base' counter with cntr_type = 1073939712.

For example:
("Buffer cache hit ratio" / "Buffer cache hit ratio base") *100
will give you the 'Buffer cache hit ratio'

SELECT object_name,counter_name,cntr_value,cntr_type
FROM sys.dm_os_performance_counters
WHERE (counter_name = 'Buffer cache hit ratio'
                OR counter_name = 'Buffer cache hit ratio base')
               AND object_name like  '%:Buffer Manager%'

Per Second:
These kind of counters store cumulative values, meaning the
value must be compared at 2 diffrerent times by calculating the
differences between the values.For instance,get a counter value,
save it, then get it again after 5 seconds for example and the right
final counter value will be:
(@SecondCounterValue-@FirstCounterValue)/5 seconds.

These counters have cntr_type = 272696576.

FROM sys.dm_os_performance_counters
WHERE counter_name = 'SQL Compilations/sec'
              AND object_name like '%SQL Statistics%'

Point In Time:
These are point-in-time counters.They hold the value at the
current point-in-time.These counters have cntr_type = 65792.

FROM sys.dm_os_performance_counters
WHERE counter_name = 'User Connections'
              AND object_name Like '%General Statistics%'

Example for calculating the Buffer cache hit ratio:

SELECT (a.Val*1.0/b.BaseValue)*100.0 as [BufferCacheHitRatio]
SELECT cntr_value as Val
FROM sys.dm_os_performance_counters
WHERE counter_name = 'Buffer cache hit ratio'
               AND object_name like '%Buffer Manager%'
) as a
SELECT cntr_value as BaseValue
FROM sys.dm_os_performance_counters
WHERE counter_name = 'Buffer cache hit ratio base'
               AND object_name like '%:Buffer Manager%'
) as b

By knowing the types of the counters, you can write a SP
that will collect for you the performance data of your server.
Here is a very good example of such SP.


  1. Hi there,
    How did you draw this awesome graph?
    Do you use excel or some specific tool?


  2. You can use Cacti

    WHEN CntrBase.cntr_value = 0 THEN 0
    ELSE CAST(CntrVal.cntr_value AS FLOAT) / CntrBase.cntr_value
    END AS CounterValueRatio
    sys.dm_os_performance_counters CntrVal
    JOIN sys.dm_os_performance_counters CntrBase ON CntrVal.object_name = CntrBase.object_name
    AND CntrVal.instance_name = CntrBase.instance_name
    AND (
    RTRIM(CntrVal.counter_name) + N' Base' = CntrBase.counter_name
    OR (
    CntrVal.counter_name = N'Worktables From Cache Ratio'
    AND CntrBase.counter_name = N'Worktables From Cache Base'
    WHERE CntrVal.cntr_type = 537003264