Interpreting the counter values from sys.dm_os_performance_counters

[mssql-support]

First published on MSDN on Sep 23, 2013

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