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:

Value/Base:
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.

SELECT *
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.

SELECT *
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]
FROM (
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
CROSS JOIN
(
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.

3 comments:

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

    Cheers

    ReplyDelete
  2. You can use Cacti
    http://www.cacti.net/

    ReplyDelete
  3. SELECT
    CntrVal.object_name,
    CntrVal.counter_name,
    CntrVal.instance_name,
    CASE
    WHEN CntrBase.cntr_value = 0 THEN 0
    ELSE CAST(CntrVal.cntr_value AS FLOAT) / CntrBase.cntr_value
    END AS CounterValueRatio
    FROM
    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

    ReplyDelete