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.

[sourcecode language=”SQL” wraplines=”false”]
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

[/sourcecode]