perfmon

Understanding the cntr_type values from sys.dm_os_performance_counters – SQL server 2012

Before using the cntr_value from sys.dm_os_performance_counters , we need to know the cntr_type and what the cntr_type  value is indicating.

  1. 65792: This counter value shows the last observed value directly.
  2. 272696576 : This counter value represents a rate metric. The cntr_value is cumulative.
  3. 1073874176 :This counter value represents an average metric. The cntr_value is cumulative.  To calculate actual value you need to use base values.

EX: Average Latch Wait Time (ms) for the interval = (A2 – A1) / (B2 – B1)
= (14272 – 14257) / (360 – 359)
= 15.00 ms

A (A1, A2) : Average Latch Wait Time (ms)

B (B1,B2) :Average Latch Wait Time Base

4.   537003264 : This counter value represents a fractional value as a ratio to its        corresponding  counter value. This is not final value , we have to use base value    (1073939712 cntr_type) to calculate the actual value.

EX: Hit ratio %  = 100 * MSSQL$SQLSVR:Buffer Manager\Buffer cache hit ratio /                                             MSSQL$SQLSVR:Buffer Manager\Buffer cache hit ratio base
= 100 * 2911 / 3170
= 91.83%

5. 1073939712 : This counter value is raw data that is used as the denominator of a counter that presents a instantaneous arithmetic fraction.

For ex: Buffer cache hit ratio base value is the base for the MSSQL$SQLSVR:Buffer Manager\Buffer cache hit ratio calculation. 

Find Buffer cache hit ratio value using TSQL ? – SQL server 2012

— Below script will help you to find the Buffer cache hit ratio value in %.

–Method 1:

SELECT [object_name],
[counter_name],
[cntr_value] FROM sys.dm_os_performance_counters
WHERE [object_name] LIKE ‘%Manager%’
AND [counter_name] = ‘Page life expectancy’

 

—Method 2:

DECLARE @Buffer_cache_hit_ratio INT;
DECLARE @Buffer_cache_hit_ratio_base INT;
SELECT @Buffer_cache_hit_ratio = cntr_value FROM sys.dm_os_performance_counters
WHERE [object_name] LIKE ‘%Buffer Manager%’ AND [counter_name] = ‘Buffer cache hit ratio’
SELECT @Buffer_cache_hit_ratio_base = cntr_value FROM sys.dm_os_performance_counters
WHERE [object_name] LIKE ‘%Buffer Manager%’ AND [counter_name] = ‘Buffer cache hit ratio base’
select 100*@Buffer_cache_hit_ratio/@Buffer_cache_hit_ratio_base as ‘Buffer_cache_hit_ratio_value’

—-Method 3:

SELECT (a.cntr_value * 1.0 / b.cntr_value) * 100.0 as BufferCacheHitRatio
FROM sys.dm_os_performance_counters a
JOIN (SELECT cntr_value,OBJECT_NAME
FROM sys.dm_os_performance_counters
WHERE counter_name = ‘Buffer cache hit ratio base’
AND OBJECT_NAME = ‘SQLServer:Buffer Manager’) b ON a.OBJECT_NAME = b.OBJECT_NAME
WHERE a.counter_name = ‘Buffer cache hit ratio’
AND a.OBJECT_NAME = ‘SQLServer:Buffer Manager’