skreebydba

In the environment I work we collect a number of performance metrics from the sys.dm_os_performance_counters DMO. This data is useful for troubleshooting and historical analysis. Analyzing this data presents a challenge because SQL Server stores any metric measured per second as a cumulative counter that is reset when the instance starts up. To gather meaningful information from the data, you must take the delta between two measurements taken at different times and divide by the seconds elapsed between the two measurements.

I was reminded of this after reading an excellent article by Jonathan Kehayias on the debate over buffer cache hit ratio and page reads/sec. Here is example of calculating deltas using page reads/sec as an example:

USE master
GO

-- Declare variables

DECLARE @maxval BIGINT
DECLARE @maxdate DATETIME
DECLARE @minval BIGINT
DECLARE @mindate DATETIME
DECLARE @deltaval INT
DECLARE @deltasec INT
DECLARE @pagereadssec INT

-- Collect the mininum values for page reads/sec and run date

SELECT @minval = cntr_value, @mindate = GETDATE()
FROM sys.dm_os_performance_counters
WHERE counter_name = 'Page reads/sec'

-- Wait for 1 minute
WAITFOR DELAY '00:01:00'

-- Collect the maximum value for page reads and run date
SELECT @maxval = cntr_value, @maxdate = GETDATE()
FROM sys.dm_os_performance_counters
WHERE counter_name = 'Page reads/sec'

-- Calculate the number of page reads in the time elapsed
SET @deltaval = @maxval - @minval

-- Calculate the number of seconds elapsed
SET @deltasec = DATEDIFF(SS, @mindate, @maxdate)

-- Calculate page reads/sec
SET @pagereadssec = @deltaval / @deltasec

-- Select the minimum value, maximum value and the three calulated values to confirm the process is working correctly
-- This illustrates the cumulative nature of the sys.dm_os_performance_counters values

SELECT @maxval AS [Maximum Value], 
@minval AS [Minimum Value], 
@deltasec AS Seconds, 
@deltaval AS [Page Reads], 
@pagereadssec AS [Page Reads/Sec]

Here is an example of the results from an extremely active instance:

From these results, you can see how large the cumulative counters for these metrics can get and why delta calculations are so important. In my next post, I will show how to save these values historically and a procedure for calculating the per-second value over different time frames.