Archive
Tracking and Calculating Wait Times – Part II
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.
Tracking and Calculating Wait Times – Part I
Several weeks ago, I took a call from a vended application investigating slowness that occurred on a regular basis on Wednesdays. Members of my team had looked at the performance of the app from a SQL Server perspective during the slow period and could not find anything out of line. The app had also worked with the Windows, Storage and Network teams, and no red flags were found. The vendor had asked if we could provide information about SQL Server wait times.
The data they were interested in is available from sys.dm_os_waiting_tasks, but the wait time values are cumulative so the raw values are not very useful. Fortunately, one of my former co-workers (I owe him a beer) set up a process that created the table scripted below:
USE [Your Database Name] GO /****** Object: Table [dbo].[Your Table Name] Script Date: 06/18/2012 21:27:17 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[Your Table Name] ([WAT_WAIT_TYPE_NM] [nvarchar](120) NOT NULL, [WAT_WAITING_TASKS_NBR] [bigint] NOT NULL, [WAT_WAIT_TM_MS_NBR] [bigint] NOT NULL, [WAT_MAX_WAIT_TIME_MS_NBR] [bigint] NOT NULL, [WAT_SIGNAL_WAIT_TIME_MS_NBR] [bigint] NOT NULL, [WAT_CAPTURE_DT] [datetime] NOT NULL ) ON [PRIMARY] GO ALTER TABLE [dbo].[Your Table Name] ADD DEFAULT (getdate()) FOR [WAT_CAPTURE_DT] GO
The process runs the following stored procedure hourly from an agent job:
USE [Your Database Name] GO /****** Object: StoredProcedure [dbo].[Your Procedure Name] Script Date: 06/18/2012 21:28:15 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE PROCEDURE [dbo].[Your Procedure Name] AS SET NOCOUNT ON ; INSERT INTO [dbo].[DUT_WAIT_STATS] ( WAT_WAIT_TYPE_NM, WAT_WAITING_TASKS_NBR, WAT_WAIT_TM_MS_NBR, WAT_MAX_WAIT_TIME_MS_NBR, WAT_SIGNAL_WAIT_TIME_MS_NBR, WAT_CAPTURE_DT) SELECT [wait_type], [waiting_tasks_count], [wait_time_ms], [max_wait_time_ms], [signal_wait_time_ms], GETDATE() FROM sys.dm_os_wait_stats GO
I would recommend using a process like this to provide the ability to troubleshoot wait times historically as it is simple to set up and is very lightweight. We are storing 6 months' worth of data and the table is less than 4mb in size.
This gave me the data I was looking for, but because the wait times are cumulative I still needed to calculate the deltas. I will cover the process I put together in my next post.