The performance counters exposed by SQL Server are invaluable tools for monitoring various aspects of the instance health. The counter data is exposed as a shared memory object for the windows performance monitoring tools to query. It is also available as a Dynamic Management View (DMV) within SQL Server, namely, sys.dm_os_performance_counters. The VIEW SERVER STATE permission is required to be able to query this view.

The counter data exposed in the view are in a raw form. This needs to be interpreted appropriately before it can be used. The cntr_type column value indicates how the values have to be interpreted. There were some questions around the values reported by this column which prompted this blog post. In this article, we will look at how to interpret the counter values.

The columns exposed by the view are described in the MSDN documentation but is reproduced here for reference.

Column name

Data type

Description

object_name

nchar(128)

Category to which this counter belongs.

counter_name

nchar(128)

Name of the  counter.

instance_name

nchar(128)

Name of the  specific instance of the counter. Often contains the database name.

cntr_value

bigint

Current value  of the counter.

         

    Note   

   

For per-second counters, this value is cumulative. The
    rate value must be calculated by sampling the value at discrete time
    intervals. The difference between any two successive sample values is equal
    to the rate for the time interval used.

   

cntr_type

int

Type of counter as defined by the Windows performance architecture. See WMI
  Performance Counter Types
on MSDN or your Windows Server documentation for more information on performance counter types.

 

The type of each counter is indicated in the cntr_type column as a decimal value. The distinct values used by all versions between SQL Server 2005 and SQL Server 2012 are the following

 

Decimal    

Hexadecimal    

Counter type define

1073939712

 0x40030500

PERF_LARGE_RAW_BASE

537003264 
 

 0x20020500

PERF_LARGE_RAW_FRACTION

1073874176

 0x40020500

PERF_AVERAGE_BULK

272696576 
 

 0x10410500

PERF_COUNTER_BULK_COUNT

65792          

 0x00010100

PERF_COUNTER_LARGE_RAWCOUNT

 

Let us look at them individually.

1)     PERF_LARGE_RAW_BASE

                Decimal Value                   : 1073939712
                Hexadecimal value            : 0x40030500

This counter value is raw data that is used as the denominator of a counter that presents a instantaneous arithmetic fraction. See PERF_LARGE_RAW_FRACTION for more information.

   Eg :

object_name

counter_name

instance_name

cntr_value

cntr_type

MSSQL$SQLSVR:Buffer Manager

Buffer cache hit ratio base

 

3170

1073939712

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

 

2)     PERF_LARGE_RAW_FRACTION

                Decimal Value                   : 537003264
                Hexadecimal value          : 0x20020500

   This counter value represents a fractional value as a ratio to its corresponding PERF_LARGE_RAW_BASE counter value.

   Eg :

object_name

counter_name

instance_name

cntr_value

cntr_type

MSSQL$SQLSVR:Buffer Manager

Buffer cache hit ratio

 

2911

537003264

                               

                Using the value here and the base value from the previous example, we can now calculate the MSSQL$SQLSVR:Buffer Manager\Buffer cache hit ratio as follows

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%

 

3)     PERF_AVERAGE_BULK

                Decimal Value                   : 1073874176
                Hexadecimal value            : 0x40020500

This counter value represents an average metric. The cntr_value is cumulative. The base value of type PERF_LARGE_RAW_BASE is used which is also cumulative. The value is obtained by first taking two samples of both the PERF_AVERAGE_BULK value A1 and A2 as well as the  PERF_LARGE_RAW_BASE value B1 and B2. The difference between A1 and A2 and B1 and B2 are calculated. The final value is then calculated as the ratio of the differences. The example below will help make this clearer.

    Eg :

Sample 1

object_name

counter_name

instance_name

cntr_value

cntr_type

 

MSSQL$SQLSVR:Latches

Average Latch Wait Time (ms)

 

14257

1073874176

<== A1

MSSQL$SQLSVR:Latches

Average Latch Wait Time Base

 

359

1073939712

<== B1

               

Sample 2

object_name

counter_name

instance_name

cntr_value

cntr_type

 

MSSQL$SQLSVR:Latches

Average Latch Wait Time (ms)

 

14272

1073874176

<== A2

MSSQL$SQLSVR:Latches

 

Average Latch Wait Time Base

 

 

 

360

 

1073939712

 

<== B2

 

               

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

               

4)     PERF_COUNTER_BULK_COUNT

                Decimal Value                   : 272696576
                Hexadecimal value            : 0x10410500

This counter value represents a rate metric. The cntr_value is cumulative. The value is obtained by taking two samples of the PERF_COUNTER_BULK_COUNT value. The difference between the sample values is divided by the time gap between the samples in seconds. This provides the per second rate.

   Eg : For this example, I obtain the ms_ticks column from sys.dm_os_sys_info for calculation. You may use any method of choice to determine the difference in time between the counter value snapshots including getdate()

Sample 1

ms_ticks

object_name

counter_name

instance_name

cntr_value

cntr_type

488754390

MSSQL$SQLSVR:Databases

Transactions/sec

AdvWrks

1566

272696576

Sample 2                                

ms_ticks

object_name

counter_name

instance_name

cntr_value

cntr_type

488755468

MSSQL$SQLSVR:Databases

Transactions/sec

AdvWrks

2055

272696576

                                               

The value for Transactions/sec for the interval = (Value2 - Value1) / (seconds between samples)
                                                                          = (Value2 - Value1) / ((ms_value2 - ms_value1) / 1000)
                                                                          = (2055 - 1566) / ((488755468-488754390) / 1000)
                                                                          = 489 transactions/sec

 

5)     PERF_COUNTER_LARGE_RAWCOUNT

                Decimal Value                   : 65792
                Hexadecimal value            : 0x00010100

   This counter value shows the last observed value directly. Primarily used to track counts of objects.

  Eg :

object_name

counter_name

instance_name

cntr_value

cntr_type

MSSQL$SQLSVR:Buffer Manager

Total pages

 

5504

65792

               The value of the counter MSSQL$SQLSVR:Buffer Manager\Total pages = 5504.

               

 

Related links :

The sys.dm_os_performance_counters DMV documentation

                sys.dm_os_performance_counters (Transact-SQL)         
                http://msdn.microsoft.com/en-us/library/ms187743%28v=sql.110%29.aspx

More information about the various SQL Server counters and what information they convey.

                Use SQL Server Objects
                http://technet.microsoft.com/en-us/library/ms190382.aspx

 

Information about the performance counter defined values from Microsoft Performance Counter Query Protocol documentation

                2.2.4.2  _PERF_COUNTER_REG_INFO
                http://msdn.microsoft.com/en-us/library/cc238313.aspx

 

Ajith Krishnan | Escalation Engineer | Microsoft SQL Server Support