In a previous post, there was a comparison between sysperfinfo and sys.dm_os_performance_counters which included the use of a simple query.  The query just returned the contents of the dynamic management view, but didn’t provide the data from the view in a manner that immediately useful for all performance counters.

The reason the data isn’t entirely useful when queried is that many performance counters aggregates and others require knowledge of a base value in order to calculate their current true value.  In this post, we’ll look at the types of counters that are available from the views within SQL Server and how to extract and use the data within them.

Counter Types

Before any values can be calculated out of sysperfinfo and sys.dm_os_performance_counters, the first thing to look at are the counter types available from the views.  With the views there are five different types of counters.  These types, which are presented numerically, are:

• PERF_COUNTER_LARGE_RAWCOUNT (65792) – Returns the last observed value for the counter.
• PERF_COUNTER_BULK_COUNT (272696576) – Average number of operations completed during each second of the sample interval.
• PERF_LARGE_RAW_BASE (1073939712) – Base value found in the calculation of PERF_AVERAGE_BULK.
• PERF_AVERAGE_BULK (1073874176 and 537003264) – Number of items processed, on average, during an operation. This counter type displays a ratio of the items processed (such as bytes sent) to the number of operations completed, and requires a base property with PERF_LARGE_RAW_BASE as the counter type.

Calculating Counter Values

As the description for each counter type indicates, there are some counter types where the values are presented in readily consumable formats.  For most of the others, though, the value for the counter needs to be compared to some other value in order to calculate the current state of the counter.  The calculations for the counters can be broken down into four categories:

• Point in Time
• Delta
• Ratio
• Delta Ratio

Point in Time Counters

The first counter calculation is for the Point in Time counters.  These counters represent the current value for the counter and can be returned as is, without any modification. These are identified by counter type 65792. Examples of these counters are Buffer Manager\Page life expectancy, Memory Manager\Memory Grants Pending, and Wait Statistics\Page IO latch waits.

Delta Counters

The second calculation for counters is the the Delta counters.  These counters, which use counter type 272696576, compare the current value to a previously known value for the counter.  The value is then averaged over the length of time between the current value and the snapshot value.  The calculation for the delta can be represented as (current_value – snapshot_value) / snapshot_length_sec.  For instance, a common method for these counters is to take a sample every 15 seconds and the determine the average of the difference over the 15 seconds.  Some common counters in this category are Access Methods\Full Scans/sec, Buffer Manager\Page reads/sec, and General Statistics\Logins/sec.

Ratio Counters

The next type of calculation is the Ratio counters.  The calculations for these counters, with counter type 537003264, rely on comparing the counter to a base value, which are counter type 1073939712.  The calculation for this counter takes the current value divided by the base counter, the calculation is expressed by current_value / base_counter_value.  Examples of counters of this type are Buffer Manager\Buffer cache hit ratio, Databases\Log Cache Hit Ratio, and Plan Cache\Cache Hit Ratio.

Delta Ratio Counters

The last type of calculation is a hybrid of the previous two calculation types.  These values are calculated by using a snapshot with delta values in them and also by calculating a ratio between the delta values.  The numerator of the ratio, identified with counter type 1073874176, compares the current value of the counter to a snapshot of the value.  Then the denominator, counter type 1073939712, is the delta between the current and snapshot values.  Since this calculation is based on a delta between two times, the resulting ration is then divided by the number of seconds between the snapshot and current value.  As an expression, this (current_value – snapshot) / (current_base_counter_value – snapshot_base_counter_value) / snapshot_length_sec.  Some common examples of counters of this type are Latches\Average Latch Wait Time (ms), Locks\Average Wait Time (ms), and Transactions\Update conflict ratio.

Base Counters

As mentioned in two of the counter calculations, determining the value for some counters require a comparison to a base counter.  The base counter typically identifies the max value that the related counter return.  There are two common methods for determing base counters.  The first is the counter type 1073939712.  All counters of this type are base counters.  The second method is used to associate the base counter to the related counter.  In most cases, base counters are the name of the related counter with the word “base” concatenated to the end of the counter name.

There are some expectations to the use of “base” to determine the related counters.  While there isn’t an explanation for this deviation, there are some counters whose base counter name vary greatly from the naming standard.  These counters are listed in Table 1.

Table 1 – Counters with Base Counters

Some of the counters in the table may not be too familiar, since most of them were introduced in SQL Server 2012. But the queries in this post will work with SQL Server from 2005 through 2012.

Counters Snapshot

To prepare for calculating performance counters from SQL Server, a snapshot table with performance counters is required.  This table needs all of the counters of types 272696576 and 1073874176.  These are the counters that use ratios for calculating the true counter value.  Along with those counters, all of the 1073874176 counters need their associated base counters (type 1073939712) to complete the later calculations.  Refer to the previous section on base counters for issues that can occur in matching base counters with their related counters.  The last thing needed in the snapshot is the current time to assist in determining the number of seconds from the the snapshot and the later final counter calculations.  The query for creating a temporary table with a snapshot of the performance counters is included in Listing 1.

--Listing 1 – Retrieve Base Table
IF OBJECT_ID('tempdb..#Baseline') IS NOT NULL
DROP TABLE #Baseline
SELECT
GETDATE() AS sample_time
,pc1.object_name
,pc1.counter_name
,pc1.instance_name
,pc1.cntr_value
,x.cntr_value AS base_cntr_value
INTO #Baseline
FROM sys.dm_os_performance_counters pc1
OUTER APPLY (
SELECT cntr_value
FROM sys.dm_os_performance_counters pc2
WHERE pc2.cntr_type = 1073939712
AND UPPER(pc1.counter_name) = UPPER(
CASE pc2.counter_name
WHEN 'Worktables From Cache Base' THEN 'Worktables From Cache Ratio'
WHEN 'Avg. Length of Batched Writes BS' THEN 'Avg. Length of Batched Writes'
WHEN 'Avg. Time Between Batches Base' THEN 'Avg. Time Between Batches (ms)'
WHEN 'Avg. Time to Write Batch Base' THEN 'Avg. Time to Write Batch (ms)'
WHEN 'Time delete FileTable item BASE' THEN 'Avg time delete FileTable item'
WHEN 'Time FileTable enumeration BASE' THEN 'Avg time FileTable enumeration'
WHEN 'Time FileTable handle kill BASE' THEN 'Avg time FileTable handle kill'
WHEN 'Time move FileTable item BASE' THEN 'Avg time move FileTable item'
WHEN 'Time per file I/O request BASE' THEN 'Avg time per file I/O request'
WHEN 'Time per file I/O response BASE' THEN 'Avg time per file I/O response'
WHEN 'Time rename FileTable item BASE' THEN 'Avg time rename FileTable item'
WHEN 'Time to get FileTable item BASE' THEN 'Avg time to get FileTable item'
WHEN 'Time update FileTable item BASE' THEN 'Avg time update FileTable item'
WHEN 'Average Latch Wait Time Base' THEN 'Average Latch Wait Time (ms)'
WHEN 'Average Wait Time Base' THEN 'Average Wait Time (ms)'
ELSE SUBSTRING(pc2.counter_name, 1, PATINDEX('% Bas%', pc2.counter_name))
END)
AND pc1.[object_name] = pc2.[object_name]
AND pc1.instance_name = pc2.instance_name) x
WHERE pc1.cntr_type IN (272696576,1073874176)

Final Counter Calculations

With the snapshot of the counters collected, the next step is to calculate the final counter calculations.  Usually when people calculate counter values, there is a 15 second delay between the snapshot and the final calculation.  Since the snapshot includes a timestamp, the length of delay between the two queries is not critical to performing the calculations.  In the final counter query, provided in Listing 2, there are three key pieces to consider.  First, the same base counter logic used in the snapshot will need to be used in this query as well to compare the counters to their current base counters.  Next, the snapshot values, included in the #baseline temporary table, need to be joined to the results.  Lastly, the calculations from the Calculating Counter Values section need to be performed.  In this query, they are calculated in a single CASE statement.

--Listing 2 – Calculate Counter Values
SELECT pc1.object_name
,pc1.counter_name
,pc1.instance_name
,CASE WHEN pc1.cntr_type = 65792 THEN pc1.cntr_value
WHEN pc1.cntr_type = 272696576 THEN COALESCE((1.*pc1.cntr_value-x.cntr_value)
/NULLIF(DATEDIFF(s, sample_time, GETDATE()),0),0)
WHEN pc1.cntr_type = 537003264 THEN COALESCE((1.*pc1.cntr_value)
/NULLIF(base.cntr_value,0),0)
WHEN pc1.cntr_type = 1073874176 THEN COALESCE((1.*pc1.cntr_value-x.cntr_value)
/NULLIF(base.cntr_value-x.base_cntr_value,0)
/NULLIF(DATEDIFF(s, sample_time, GETDATE()),0),0)
END as real_cntr_value
FROM sys.dm_os_performance_counters pc1
OUTER APPLY (SELECT cntr_value, base_cntr_value, sample_time
FROM #Baseline b
WHERE b.object_name = pc1.object_name
AND b.counter_name = pc1.counter_name
AND b.instance_name = pc1.instance_name
) x
OUTER APPLY (
SELECT cntr_value
FROM sys.dm_os_performance_counters pc2
WHERE pc2.cntr_type = 1073939712
AND UPPER(pc1.counter_name) = UPPER(
CASE pc2.counter_name
WHEN 'Worktables From Cache Base' THEN 'Worktables From Cache Ratio'
WHEN 'Avg. Length of Batched Writes BS' THEN 'Avg. Length of Batched Writes'
WHEN 'Avg. Time Between Batches Base' THEN 'Avg. Time Between Batches (ms)'
WHEN 'Avg. Time to Write Batch Base' THEN 'Avg. Time to Write Batch (ms)'
WHEN 'Time delete FileTable item BASE' THEN 'Avg time delete FileTable item'
WHEN 'Time FileTable enumeration BASE' THEN 'Avg time FileTable enumeration'
WHEN 'Time FileTable handle kill BASE' THEN 'Avg time FileTable handle kill'
WHEN 'Time move FileTable item BASE' THEN 'Avg time move FileTable item'
WHEN 'Time per file I/O request BASE' THEN 'Avg time per file I/O request'
WHEN 'Time per file I/O response BASE' THEN 'Avg time per file I/O response'
WHEN 'Time rename FileTable item BASE' THEN 'Avg time rename FileTable item'
WHEN 'Time to get FileTable item BASE' THEN 'Avg time to get FileTable item'
WHEN 'Time update FileTable item BASE' THEN 'Avg time update FileTable item'
WHEN 'Average Latch Wait Time Base' THEN 'Average Latch Wait Time (ms)'
WHEN 'Average Wait Time Base' THEN 'Average Wait Time (ms)'
ELSE SUBSTRING(pc2.counter_name, 1, PATINDEX('% Bas%', pc2.counter_name))
END)
AND pc1.[object_name] = pc2.[object_name]
AND pc1.instance_name = pc2.instance_name) base
WHERE pc1.cntr_type IN (65792,272696576,537003264,1073874176)

Summary

If you haven’t been using sysperfinfo and sys.dm_os_performance_counters, now’s a good time to start.  These views provide a great insight into the performance counters on your database platform without having to gain access to the server to observe them.  While it takes more than a simple query to get the needed results, the query provided in this post provides the needed logic making it’s use quite simple indeed.