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.
