skreebydba

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.