skreebydba

In my last post, I described the process we use to collect wait information on our systems. Because SQL Server stores cumulative values, the raw wait time data is not very useful. The following script shows how to pull daily data and calculate the deltas.

This script was written for a vended application that has been experiencing slow response times. The vendor asked for wait time information to see if there was correlation between increased wait times and the slow down.

USE [Your Database Name]
GO

-- Create the necessary temp tables and variables

CREATE TABLE #waits
(waittype VARCHAR(100)
,waitdate DATETIME
,waittime BIGINT)

CREATE TABLE #waitsnew
(waittypenew VARCHAR(100)
,waitdatenew DATETIME
,waittimenew BIGINT)

CREATE TABLE #waitsold
(waittypeold VARCHAR(100)
,waitdateold DATETIME
,waittimeold BIGINT)

CREATE TABLE #waitsdelta
(waittypedelta VARCHAR(100)
,waitdatedelta DATETIME
,waittimedelta BIGINT)

DECLARE @olddate DATETIME
DECLARE @newdate DATETIME
DECLARE @totalcount INT
DECLARE @oldcount INT
DECLARE @newcount INT
DECLARE @loopcount INT

-- initialize the @loopcount variable
SET @loopcount = 1

-- populate the #waits temp table with data for the date range desired
-- DATEDIFF(dd, 0, WAT_CAPTURE_DT)) returns the date without the time
-- because the wait time value is cumulative, the max value will be the 
-- total count for the day
-- exclude system waits using WHERE clause from Jonathan Kehayias' blog post
-- http://www.simple-talk.com/sql/performance/a-performance-troubleshooting-methodology-for-sql-server/

/****** Script for SelectTopNRows command from SSMS ******/
INSERT INTO #waits
SELECT WAT_WAIT_TYPE_NM, DATEADD(dd, 0, DATEDIFF(dd, 0, WAT_CAPTURE_DT)), MAX(WAT_WAIT_TM_MS_NBR) FROM [Your Database Name].[dbo].[Your Table Name]
WHERE WAT_CAPTURE_DT > '2012-06-01'
AND WAT_WAIT_TYPE_NM NOT IN -- filter out additional irrelevant waits
( 'SLEEP_TASK', 'BROKER_TASK_STOP', 'BROKER_TO_FLUSH',
'SQLTRACE_BUFFER_FLUSH','CLR_AUTO_EVENT', 'CLR_MANUAL_EVENT', 
'LAZYWRITER_SLEEP', 'SLEEP_SYSTEMTASK', 'SLEEP_BPOOL_FLUSH',
'BROKER_EVENTHANDLER', 'XE_DISPATCHER_WAIT', 'FT_IFTSHC_MUTEX',
'CHECKPOINT_QUEUE', 'FT_IFTS_SCHEDULER_IDLE_WAIT', 
'BROKER_TRANSMITTER', 'FT_IFTSHC_MUTEX', 'KSOURCE_WAKEUP',
'LAZYWRITER_SLEEP', 'LOGMGR_QUEUE', 'ONDEMAND_TASK_QUEUE',
'REQUEST_FOR_DEADLOCK_SEARCH', 'XE_TIMER_EVENT', 'BAD_PAGE_PROCESS',
'DBMIRROR_EVENTS_QUEUE', 'BROKER_RECEIVE_WAITFOR',
'PREEMPTIVE_OS_GETPROCADDRESS', 'PREEMPTIVE_OS_AUTHENTICATIONOPS',
'WAITFOR', 'DISPATCHER_QUEUE_SEMAPHORE', 'XE_DISPATCHER_JOIN',
'RESOURCE_QUEUE' )
AND WAT_WAIT_TM_MS_NBR > 0
GROUP BY WAT_WAIT_TYPE_NM, DATEADD(dd, 0, DATEDIFF(dd, 0, WAT_CAPTURE_DT))
ORDER BY 2, 3 DESC 

-- set the loop limit equal to the number of rows returned above
SET @totalcount = @@ROWCOUNT

-- run a loop to calculate the delta value for each day
WHILE @loopcount <= @totalcount
BEGIN

-- select the minimum date from the #waits table
SELECT @olddate = MIN(waitdate)
FROM #waits

-- select the next date from the #waits table
SET @newdate = DATEADD(dd,1,@olddate)

-- insert the row for the old date into #waitsold
INSERT INTO #waitsold
SELECT * FROM #waits
WHERE waitdate = @olddate

-- set @oldcount equal the row count 
SET @oldcount = @@ROWCOUNT

-- insert the row for the new date into #waits
INSERT INTO #waitsnew
SELECT * FROM #waits
WHERE waitdate = @newdate

-- join #waitsnew and #waitsold, inserting the wait type, date 
-- and delta between the new and old times into #waitsdelta
INSERT INTO #waitsdelta
SELECT n.waittypenew, n.waitdatenew, (n.waittimenew - o.waittimeold)
FROM #waitsnew n
INNER JOIN #waitsold o
ON n.waitdatenew = @newdate
AND n.waittypenew = o.waittypeold
WHERE o.waitdateold = @olddate

-- delete the old data from #waits
DELETE FROM #waits
WHERE waitdate = @olddate

-- reset the old and new dates to the next pair of values
SET @olddate = @newdate
SET @newdate = DATEADD(DD,1,@newdate)

-- increment the @loopcount 
SET @loopcount = @loopcount + @oldcount

END

-- select the delta values from #waitsdelta
SELECT waittypedelta, waitdatedelta, (waittimedelta) AS waittimedelta
FROM #waitsdelta
ORDER BY waittypedelta, waitdatedelta

-- clean up temp tables
DROP TABLE #waits
DROP TABLE #waitsold
DROP TABLE #waitsnew
DROP TABLE #waitsdelta

One thing to keep in mind is that the wait time values are zeroes out when the instance restarts. So if the instance restarts, you will end up with negative values for your wait times. Because I am running this as an ad hoc query, I can work around this possibility. If you wanted to automate the process, I would suggest taking this into consideration.

I learned a lot working on this problem, and I hope someone out there can make use of it.