» » How to use performance counters in sys.dm_os_performance_counters DMV

How to use performance counters in sys.dm_os_performance_counters DMV

Edit

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
simpulan 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.