Archive
Calculating Deltas for sys.dm_os_performance_counters, Part II
In my last post, I showed how to calculate the values of per-second metrics from the sys.dm_os_performance_counters DMO. In this post, I will show how to save those metrics and calculate the page reads/sec value for different time frames.
For this demonstration, I created a table with the following definition:
CREATE TABLE pageread_history (insertdate DATETIME ,pagereads BIGINT)
I then created a job, scheduled to execute every minute, to run the following code:
INSERT INTO pageread_history SELECT GETDATE(), cntr_value FROM sys.dm_os_performance_counters WHERE counter_name = 'Page reads/sec'
Finally, in order to generate some non-trivial values, I executed the following code:
USE primenumbers GO -- Clear the buffer cache before each execution DBCC DROPCLEANBUFFERS -- Select every row from a million-row table SELECT * FROM primenumbers -- Execute the query 100 times GO 100
This query will force SQL Server to read all of the pages in the table from disk every time the query executes, generating page read metrics. DBCC DROPCLEANBUFFERS requires SA access and clears the buffer cache, forcing physical reads. It should NOT be run on a production system or any system on which you care about performance. I ran this on my localhost in order to generate some page read volume.
To calculate page reads/sec using the values stored in the table, I created the following stored procedure:
USE testdb GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- ============================================= -- Author: Frank Gill -- Create date: 2012-03-18 -- Description: Calculate page reads/sec based on input date values -- ============================================= ALTER PROCEDURE DUP_CALCULATE_PAGE_READS_SEC -- @mindate and @maxdate will be passed in on execution and @pagereadssec will be output @mindate DATETIME, @maxdate DATETIME, @pagereadssec INT OUTPUT AS BEGIN SET NOCOUNT ON; -- Declare variables DECLARE @maxval BIGINT DECLARE @minval BIGINT DECLARE @deltaval INT DECLARE @deltasec INT DECLARE @tablemaxdate DATETIME DECLARE @errormessage VARCHAR(500) -- If @maxdate or @mindate are greater than the max date in the table, NULL will be returned -- To check for this, select the MAX date value out of the table SELECT @tablemaxdate = MAX(insertdate) FROM pageread_history -- If @maxdate or @mindate are greater than @tablemaxdate, raise an error and output the MAX date value IF (@mindate > @tablemaxdate) OR (@maxdate > @tablemaxdate) BEGIN SET @errormessage = 'Both @mindate and @maxdate must be greater than ' + CONVERT(VARCHAR(20),@tablemaxdate,121) RAISERROR (@errormessage, 16, 1) -- Pull the first value from a datetime greater than or equal to @mindate SELECT TOP 1 @minval = pagereads FROM pageread_history WHERE insertdate >= @mindate -- Pull the first value from a datetime greater than or equal to @maxdate -- If @maxdate or @mindate are greater than the max date in the table, NULL will be returned SELECT TOP 1 @maxval = pagereads FROM pageread_history WHERE insertdate >= @maxdate -- Calculate the delta value with the counts returned from the table SET @deltaval = @maxval - @minval -- Calculate the number of seconds elapsed SET @deltasec = DATEDIFF(SS, @mindate, @maxdate) -- Calculate page reads/sec SET @pagereadssec = @deltaval / @deltasec END GO
While I've used page reads in the previous examples, this code can be modified to calculate any of the per-second metrics returned from sys.dm_os_performance_counters. I hope you can use the ideas presented here.
Calculating Deltas for sys.dm_os_performance_counters, Part I
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:
Instance | Max Val | Min Val | Sec | Page Reads | Page Reads/Sec |
myinst | 20494050250 | 20494046388 | 60 | 3862 | 64 |
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.