skreebydba

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.