skreebydba

In a previous post, I showed how to calculate deltas for cumulative values from sys.dm_os_performance_counters. At the end of that post, I mentioned that the counter values are reset when the instance restarts, which can result in negative delta values. For the purposes of that script I did not care about negative values.

However, we are using cumulative values from sys.dm_io_virtual_file_stats to generate a report in Reporting Services, and the negative values are mucking up our graphs. The script I used to solve the problem is listed below. As usual, I hope you can put the ideas contained to use.

USE DUDDBA_MASTER
GO

-- Declare variables and create temp tables
DECLARE @sqlstr NVARCHAR(4000)
DECLARE @servername VARCHAR(40)
DECLARE @totalrows INT
DECLARE @currentrow INT
DECLARE @status INT
DECLARE @oldreadcnt BIGINT
DECLARE @newreadcnt BIGINT
DECLARE @newdate DATETIME
DECLARE @olddate DATETIME
DECLARE @mindate DATETIME
DECLARE @instancename VARCHAR(40)
DECLARE @tempstartup DATETIME
DECLARE @SQLString NVARCHAR(500);
DECLARE @ParmDefinition NVARCHAR(500);
DECLARE @startupdateout DATETIME


CREATE TABLE #serverlist 
(servername VARCHAR(40) NOT NULL)

CREATE TABLE #IODATA
(instancename VARCHAR(8)
,databasename VARCHAR(100)
,runtime DATETIME
,readcount BIGINT
,bytesread BIGINT
,writecount BIGINT
,byteswritten BIGINT)

CREATE TABLE #IODATANEW
(databasename VARCHAR(100)
,runtime DATETIME
,readcount BIGINT
,bytesread BIGINT
,writecount BIGINT
,byteswritten BIGINT)

CREATE TABLE #IODATAOLD
(databasename VARCHAR(100)
,runtime DATETIME
,readcount BIGINT
,bytesread BIGINT
,writecount BIGINT
,byteswritten BIGINT)

-- Initialize the @newdate and @olddate variables for the inner loop
SET @newdate = CONVERT(DATE,GETDATE())
SET @olddate = DATEADD(DD,-1,@newdate)

-- Build a list of linked servers for the outer loop, excluding non-2008 instances 
INSERT INTO #serverlist
SELECT 'DUL' + s1.DPC_SQL_CLUSTER_NM 
FROM DUT_POD_SQL_SERVER s1
INNER JOIN DUT_POD_SQL_VERSION s2
ON s1.DPC_SQL_CLUSTER_NM = s2.SVR_HOST_NM
WHERE s2.SVR_SQL_VERSION_TXT LIKE '%10.%' 

-- Store the @@ROWCOUNT value from the insert statement to control the outer loop
SET @totalrows = @@ROWCOUNT
SET @currentrow = 1

-- Execute the outer loop once for each linked server
WHILE @currentrow <= @totalrows
BEGIN

	-- Pull the first linked server name from the table
	SET @servername = (SELECT TOP 1 servername FROM #serverlist)
	
	-- Run the inner loop from the current date until 2012-01-01
	WHILE @olddate > '2012-01-01'
		BEGIN
			-- Build the dynamic sql string
			SET @sqlstr = 	
			'
			-- The DUT_FILE_IO table contains cumulative IO metrics from sys.dm_io_virtual_file_stats and is populated every 5 minutes
			-- The statement below pulls the values for the current day at midnight into #IODATANEW excluding non-user databases
			INSERT INTO #IODATANEW
			SELECT FIO_DATABASE_NM
			,FIO_RUN_TS
			,SUM(FIO_NBR_READS_CNT) AS [Total Reads]
			,SUM(FIO_BYTES_READ_CNT) AS [Total Bytes Read]
			,SUM(FIO_NBR_WRITES_CNT) AS [Total Writes]
			,SUM(FIO_BYTES_WRITE_CNT) AS [Total Bytes Written]
			FROM ' + @servername + '.duddba.dbo.DUT_FILE_IO 
			WHERE DATEPART(HH,FIO_RUN_TS) = 00
			AND DATEPART(MI,FIO_RUN_TS) = 00
			AND DATEPART(SS,FIO_RUN_TS) = 00
			AND CONVERT(DATE,FIO_RUN_TS) = ' + '''' + CONVERT(VARCHAR(30),@newdate,120)  + '''' +
			'
			AND FIO_DATABASE_NM NOT IN (''DUDDBA'',''DUDREPO'',''DUDDUMMY'',''DUDTRACE'',''master'',''model'',''msdb'')
			GROUP BY FIO_DATABASE_NM, FIO_RUN_TS
			ORDER BY FIO_RUN_TS DESC, FIO_DATABASE_NM

			-- The statement below pulls the values for the previous day at midnight into #IODATAOLD excluding non-user databases			
			INSERT INTO #IODATAOLD
			SELECT FIO_DATABASE_NM
			,FIO_RUN_TS
			,SUM(FIO_NBR_READS_CNT) AS [Total Reads]
			,SUM(FIO_BYTES_READ_CNT) AS [Total Bytes Read]
			,SUM(FIO_NBR_WRITES_CNT) AS [Total Writes]
			,SUM(FIO_BYTES_WRITE_CNT) AS [Total Bytes Written]
			FROM ' + @servername + '.duddba.dbo.DUT_FILE_IO 
			WHERE DATEPART(HH,FIO_RUN_TS) = 00
			AND DATEPART(MI,FIO_RUN_TS) = 00
			AND DATEPART(SS,FIO_RUN_TS) = 00
			AND CONVERT(DATE,FIO_RUN_TS) = ' + '''' + CONVERT(VARCHAR(30),@olddate,120)  + '''' +
			'
			AND FIO_DATABASE_NM NOT IN (''DUDDBA'',''DUDREPO'',''DUDDUMMY'',''DUDTRACE'',''master'',''model'',''msdb'')
			GROUP BY FIO_DATABASE_NM, FIO_RUN_TS
			ORDER BY FIO_RUN_TS DESC, FIO_DATABASE_NM
			
			
			-- The insert statement below calculates the delta values for the IO metrics collected
			-- The series of CASE statements accounts for the possibility of a negative value
			-- If the delta value is positive, it is used
			-- If it is negative, the raw value is used
			-- This is not the ideal solution, because it does not account for the cumulative values
			-- prior to the restart but it is good enough for the purposes of the reports we are running

			INSERT INTO #IODATA
			SELECT ' + '''' + SUBSTRING(@servername,4,8) + '''' +',n.databasename, n.runtime, 
			CASE
				WHEN ((n.readcount - o.readcount) > 0)
			THEN
				(n.readcount - o.readcount) 
			ELSE
				n.readcount
			END,
			CASE
				WHEN ((n.bytesread - o.bytesread)/1045876 > 0)
			THEN
				(n.bytesread - o.bytesread)
			ELSE
				n.bytesread
			END,
			CASE
				WHEN ((n.writecount - o.writecount) > 0)
			THEN
				(n.writecount - o.writecount)
			ELSE
				n.writecount
			END,
			CASE
				WHEN ((n.byteswritten - o.byteswritten)/1045876 > 0)
			THEN
				(n.byteswritten - o.byteswritten)/1045876 
			ELSE
			n.byteswritten
			END              
			FROM #IODATANEW n  
			INNER JOIN #IODATAOLD o  
			ON n.runtime = ' + '''' + CONVERT(VARCHAR(30),@newdate,120)  + '''' +
			'AND n.databasename = o.databasename
			WHERE o.runtime = ' + '''' + CONVERT(VARCHAR(30),@olddate,120)  + '''' +
			'
			'
			
			-- Execute the dynamic SQL string
			EXEC(@sqlstr)
			
			--Reset the new and old date variables
			SET @newdate = @olddate
			SET @olddate = DATEADD(DD,-1,@olddate)
			
			-- Clear out the new and old temp tables
			DELETE FROM #IODATANEW
			DELETE FROM #IODATAOLD
			
		END
	
	-- Insert the results for each linked server into the permanent table			
	INSERT INTO DUT_FILE_IO_DELTA
	SELECT * FROM #IODATA
	
	-- Delete the current linked server from the #serverlist table	
	DELETE FROM #serverlist WHERE servername = @servername

	-- Increment the loop counter
	SET @currentrow = @currentrow + 1
	
	-- Clear out the #IODATA table
	DELETE FROM #IODATA

	-- Decrement the new and old date variables	
	SET @newdate = CONVERT(DATE,GETDATE())
	SET @olddate = DATEADD(DD,-1,@newdate)
	
END


-- Clean up the temp tables at the end of the script
DROP TABLE #serverlist
DROP TABLE #IODATA
DROP TABLE #IODATAOLD
DROP TABLE #IODATANEW