Altering wait categories in SQL Diagnostic Manager

SQL Diagnostic Manager ships with built-in categorization of wait types which are encountered in monitoring. These categories are used on both the Query Waits and Server Waits screens and can also affect whether data is captured at all. Recently I was asked whether it was possible to change the category of a wait type, or to exclude it entirely. Both are possible through minor changes in the repository.

First, a caveat. Making changes to the SQLdm repository can cause unexpected behavior. Please take a backup before making any changes, and be prepared to un-do any changes if necessary or if requested by Idera Support. With that said, let’s dive in!

SQLdm uses a fairly straightforward lookup table for wait type descriptions, categories, and help links. The two primary tables are [dbo].[WaitCategories] and [dbo].[WaitTypes].

USE [SQLdmRepository];
GO

SELECT [CategoryID]
      ,[Category]
      ,[ExcludeFromCollection]
  FROM [dbo].[WaitCategories];
GO

SELECT [WaitTypeID]
      ,[WaitType]
      ,[CategoryID]
      ,[Description]
      ,[HelpLink]
  FROM [dbo].[WaitTypes];
GO

You can move any individual wait type to a different category by changing the CategoryID value for that wait. If you want the wait type to be excluded entirely, simply set the category to Excluded. For example, given the default Excluded CategoryID of 1, the following statement would move all wait types beginning with “Preemptive” into the Excluded category:

UPDATE [dbo].[WaitTypes] 
SET [CategoryID] = 1
WHERE [WaitType] like 'PREEMPTIVE%'

You should also feel free to add to or edit the contents of the Description field to give information which is relevant to your environment. This text is shown in the Server Waits view.

Changes to these tables take effect very quickly in the desktop client, but in order for the collection workload to be fully updated, you’ll want to restart both your Management Service and Collection Service. Until that time you may see a mix of places that do and do not abide by your changes, based on whether the information is still cached. Because of the relational nature of these tables, these changes apply retroactively: if you view older data using the history browser, your new settings will still be applied.

This is a small little workaround, but one which you may find useful if you have a particular wait type cluttering up your views without adding any useful information.