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.
Hi there,
ReplyDeleteHow did you draw this awesome graph?
Do you use excel or some specific tool?
Cheers
You can use Cacti
ReplyDeletehttp://www.cacti.net/
SELECT
ReplyDeleteCntrVal.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