If You Don’t Know Where You’ve Been

If you don’t know where you’ve been, how can you expect to know where you are going.  Seems like such a simple little line and I’m sure many of us can look at history, politicians, and maybe that restaurant review that you didn’t heed when it comes to this line.  But this also applies to DBAs and more specifically to SQL Server.

In much the same way… if you don’t know what’s been happening in your SQL Server environment, you won’t know how to resolve performance problems that have occurred on the server.  One of the ways I track what’s happened on a SQL Server instance is through wait stats.  Now if you aren’t tracking wait stats and they aren’t one of the first few things that you check when monitoring performance then you need to stop reading this post and read this instead.

Wait Stat Snapshots

To facilitate wait stat monitoring, I typically take a snapshot of the current wait stats (sys.dm_os_wait_stats) on a server every 15 minutes.  This is enough time that I can roll get a good idea of what’s happened from a resource perspective in a short amount of time and I can also easily roll the value up to hour, two-hour, or full day totals.  It gives me options – we all like options.

As I’ve done in a number of posts in the past this new script will be setup to add to the DBADiagnostics database that I’ve blogged about before.

Building the Snapshot

There are two table included in the process for creating the snapshots of wait stat activity.  These are:

• Monitor.WaitStatSnapshot
• Monitor.WaitStatHistory

Here is the schema to create them:

USE [DBADiagnostics]
GO

CREATE TABLE [Monitor].[WaitStatSnapshot](
[CreateDate] [datetime] NOT NULL,
[WaitType] [nvarchar](60) NOT NULL,
[WaitingTasksCount] [bigint] NOT NULL,
[WaitTimeMs] [bigint] NOT NULL,
[MaxWaitTimeMs] [bigint] NOT NULL,
[SignalWaitTimeMs] [bigint] NOT NULL,
CONSTRAINT [PK_Monitor_WaitStatSnapshot_CreateDateWaitType]
PRIMARY KEY CLUSTERED ([CreateDate] ASC, [WaitType] ASC)
) ON [Data]
GO

CREATE TABLE [Monitor].[WaitStatHistory](
[CreateDate] [datetime] NOT NULL,
[WaitType] [nvarchar](60) NOT NULL,
[WaitingTasksCount] [bigint] NOT NULL,
[WaitTimeMs] [bigint] NOT NULL,
[MaxWaitTimeMs] [bigint] NOT NULL,
[SignalWaitTimeMs] [bigint] NOT NULL,
CONSTRAINT [PK_Monitor_WaitStatHistory_CreateDateWaitType]
PRIMARY KEY CLUSTERED ([CreateDate] ASC, [WaitType] ASC)
) ON [Data]

The general process is to insert the current values from the wait stat DMV into Monitor.WaitStatSnapshot.  After which the delta between the last two snapshots in Monitor.WaitStatSnapshot is inserted into Monitor.WaitStatHistory.

If the values in the last snapshot are smaller that those in the second to last snapshot, then the insert assumes that the wait stat DMV has been reset.  Resetting can occur either though a SQL Server service restart or a DBCC command.

Either way the procedure below will accumulate a snapshot history from the wait stat DMV.  Of courses there are a couple parameters that provide some automated cleanup.  Data in Monitor.WaitStatSnapshot is cleaned up after 1 day and the data in Monitor.WaitStatHistory is cleaned up after 90 days.  This helps prevent the data in the DBADiagnostic database from taking over when it’s neglected because everything is running perfectly.

USE [DBADiagnostics]
GO

/*============================================================
Procedure: Monitor.TrackWaitStats
Author: Jason Strate
Date: October 26, 2009

Synopsis:
This procedure takes snapshots of wait stats and compares them with previous
snapshots to determine a delta of changes over time. Raw snapshot information
is deleted on a short time span, while the delta information in the history
table is deleted over a longer time span.

============================================================
Revision History:
Date: By Description
------------------------------------------------------------

============================================================*/
CREATE PROCEDURE [Monitor].[TrackWaitStats]
(
@SnapshotDays tinyint = 1
,@HistoryDays smallint = 90
)
AS

INSERT INTO Monitor.WaitStatSnapshot
SELECT GETDATE()
, CASE wait_type WHEN 'MISCELLANEOUS' THEN 'MISCELLANEOUS' ELSE wait_type END
, SUM(waiting_tasks_count)
, SUM(wait_time_ms)
, SUM(max_wait_time_ms)
, SUM(signal_wait_time_ms)
FROM sys.dm_os_wait_stats
GROUP BY CASE wait_type WHEN 'MISCELLANEOUS' THEN 'MISCELLANEOUS' ELSE wait_type END

;WITH WaitStatCTE
AS (
SELECT CreateDate
, DENSE_RANK() OVER (ORDER BY CreateDate DESC) AS HistoryID
, WaitType
, WaitingTasksCount
, WaitTimeMs
, MaxWaitTimeMs
, SignalWaitTimeMs
FROM Monitor.WaitStatSnapshot
)
INSERT INTO Monitor.WaitStatHistory
SELECT w1.CreateDate
, w1.WaitType
, w1.WaitingTasksCount - COALESCE(w2.WaitingTasksCount,0)
, w1.WaitTimeMs - COALESCE(w2.WaitTimeMs,0)
, w1.MaxWaitTimeMs - COALESCE(w2.MaxWaitTimeMs,0)
, w1.SignalWaitTimeMs - COALESCE(w2.SignalWaitTimeMs,0)
FROM WaitStatCTE w1
LEFT OUTER JOIN WaitStatCTE w2 ON w1.WaitType = w2.WaitType
AND w1.WaitingTasksCount >= COALESCE(w2.WaitingTasksCount,0)
AND w2.HistoryID = 2
WHERE w1.HistoryID = 1

DELETE FROM Monitor.WaitStatSnapshot
WHERE CreateDate < DATEADD(d, -@SnapshotDays, GETDATE())

DELETE FROM Monitor.WaitStatHistory
WHERE CreateDate < DATEADD(d, -@HistoryDays, GETDATE())
GO

There you have it.  While I didn’t go into how you can and will use this information, the basis for collecting this data for reporting has been laid out.

Later this week I’ll post a follow-up to show how to report on this information and include a couple Reporting Services reports for digging into the data.