Interpreting the counter values from sys.dm_os_performance_counters

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.

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

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 :

  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 :

                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

Sample 2

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

Sample 2                                

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 :

               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) **        
                https://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
                https://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
                https://msdn.microsoft.com/en-us/library/cc238313.aspx

Ajith Krishnan | Escalation Engineer | Microsoft SQL Server Support