Wait Stat Categories

A few weeks ago I posted a job that I use to track wait stats in the DBA database that I bring to a number of clients.  I had promised to follow-up with a couple or three reports that could be used to monitor the values that were being aggregated in those tables.

Before getting to that, though, a quick look at the output of sys.dm_os_wait_stats might have indicated that there is a small issue with looking at the wait stats as they currently are.  There are a lot of wait types.  Specifically, my instances of SQL Server report 202 wait types for SQL Server 2005 and 485 wait types for SQL Server 2008.  Combining the two lists yields nearly 500 distinct wait types between SQL Server 2005 and 2008.  Ever notice that the MISCELLANEOUS wait type appears twice for SQL Server 2008, I bet there is a good story behind that one.

With all of those wait types, there needs to be a method to group them together to assist in researching resource performance related issues.  Of course, you already knew this and it’d probably be nice if I stopped my stalling and go to the point.

Building A Wait Type Category List

Before I go there, first I’d like to point out how I built the list and my plans for keeping it updated.  What’s the value in a classification process, if you don’t know the value of the information included?

To start with there are people that know the operations and internals of SQL Server much better than I do.  There is even a whole department at Microsoft, the CSS SQL Server Engineers,  that spends a lot time up to their elbows working with wait types.  Let’s be real, I mostly know what these guys have published. Recently, Bob Ward has started a SQL Server Wait Type Repository this is the basis for the wait type categories.  Why not just use this list and be done with it?  Well, the repository only has 22 wait types currently listed.  They are hitting the ones that provide the most value.  Their table does provide a structure and starting point for this list and I consider it the source for updates to categorizing wait types.

Next the list was augmented with the information provided in the SQL Server 2005 Waits and Queues white paper.  This is a detailed white paper the provides categories and troubleshooting information for different wait types for SQL Server 2005.  If you haven’t read this white paper you are definitely missing out and should bail on this post and go read it.

The third resource I used was the SQLServerPedia Wait Type list.  This list covers a number of the items that aren’t covered in the previous two sources and almost all of the remaining wait types.  And depending on when you read this, it may just look identical to the wait type category list since I am hoping to submit an update of one with the other.

Beyond these, there are still a few holes in the list but hopefully over time one of the three sources above or some other source will fill in the gaps and I’ll just post updates from time to time.

Providing the Wait Type Category List

Based on the above, here is the schema for a table that will hold all of the wait types that conforms to the structure I mentioned earlier:

USE [DBADiagnostics]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[Resources].[WaitTypeCategory]') AND type in (N'U'))
	DROP TABLE [Resources].[WaitTypeCategory]
GO

IF  EXISTS (SELECT * FROM sys.schemas WHERE name = N'Resources')
	DROP SCHEMA [Resources]
GO

IF NOT EXISTS (SELECT * FROM sys.schemas WHERE name = N'Resources')
    EXEC sys.sp_executesql N'CREATE SCHEMA [Resources] AUTHORIZATION [dbo]'
GO

CREATE TABLE [Resources].[WaitTypeCategory]
(
[WaitType] [nvarchar](60) NOT NULL,
[Category] [varchar](50) NULL,
[Resource] [varchar](50) NULL,
[Version] [varchar](50) NULL,
[Description] [varchar](255) NULL,
[Action] [varchar](max) NULL,
CONSTRAINT [PK_WaitTypeCategory] PRIMARY KEY CLUSTERED([WaitType] ASC)
)
GO

And then there is the wait types and categories.  Since even WordPress thinks this is too much to add to a post, here is a script with all of it together: