Customizing Wait Categories for SQL Sentry

One of the most common reasons our customers customize the SQL Server Waits Chart is to break down the Other category into something more specific. 

Up until SQL Sentry 8.4, the SQL Server Waits chart presented waits in fixed sets of Classes (Sample Mode), and Categories (History Mode). The bar chart format of Sample Mode lends itself to presenting a robust set of Classes (38 total, 35 enabled by default) including AlwaysOn, Backup, Disk IO, Latches: Buffer, Locking, Memory, and Transaction Log. In the example below we see Resource (red) and CPU (green) waits related to the Backup class, Parallel Queries class, and a handful of others.

SQL Server Waits chart in Sample Mode

In History Mode, wait stats are presented in a stacked area chart. Readability dictated that we reduce the 35 Classes to just 5 Categories - four representing each of the major resources (Network, CPU, Memory and Disk), plus an "Other" Category. These Categories are defined in the PerformanceAnalysisWaitTypeCategory table.

SQL Server Waits chart in History Mode

Note: To list all the Wait Types we collect by default and their respective category and class, run this query:

SELECT t.[Name] AS WaitTypeName,

c.[Name] AS ClassName,

g.[Name] AS CategoryName,

t.[Description]

FROM dbo.PerformanceAnalysisWaitType AS t

INNER JOIN dbo.PerformanceAnalysisWaitTypeCategory AS g

ON t.WaitTypeCategoryID = g.ID

INNER JOIN dbo.PerformanceAnalysisWaitTypeClass AS c

ON t.WaitTypeClassID = c.ID

WHERE g.Enabled = 1 AND c.Enabled = 1

ORDER BY t.[Name];

In both modes, you can view the name and wait time of specific waits that occurred at a specific point in time by hovering over the chart.

In Sample Mode, hovering over a bar displays the name and total wait time for the wait types of that class, along with the class name and total wait time captured during the sampling period. In the example below, the only Backup Class wait type represented is BACKUPIO, with waits of 791 milliseconds.

Sample Mode with Waits Details for Backup Class
 

In History Mode, hovering over an area of the chart displays the name and total wait time for all the wait types of that category along with the sample time, category name and total wait time. In the example below, the wait category is "Other", and the specific waits are BACKUPIO and CXPACKET:

History Mode with Waits Details for Other Category

Display Custom Wait Type Categories on the Dashboard

Now we have the ability to define new categories, assign specific wait types to those categories, and have them appear on the chart in History Mode.

A common use case cited by our customers has been the desire to break down the Other category into something more specific. The wait types associated with this category can be found by running this query:

SELECT t.[Name], t.[Description]

FROM dbo.PerformanceAnalysisWaitType AS t

INNER JOIN dbo.PerformanceAnalysisWaitTypeCategory AS c

ON t.WaitTypeCategoryID = c.ID

WHERE c.[Name] = 'Other'

ORDER BY [Name];

Result:

Default Wait Types in the "Other" Category

In my experience, the CXPACKET and BACKUP* wait types are the most frequently-encountered "Other" waits, so I will use them to demo this new feature.

We will create two new categories - Parallelism for CXPACKET waits, and Backup for four of the five backup-related waits (my system doesn't use tape, so the BACKUP_OPERATOR wait type is not of interest).

To accomplish our task we will update and insert rows into two SQL Sentry database tables - PerformancAnalysisWaitType and PerformanceAnalysisWaitTypeCategory.

Step 1 - Change the SortOrder of the Other Category

Because I still want the Other category to appear at the far right of the legend at the bottom of the SQL Server Waits chart, I'm going to change its SortOrder value in PerformanceAnalysisWaitType to make room for my two new custom categories.

UPDATEdbo.PerformanceAnalysisWaitTypeCategory

SETSortOrder +=2-- make room for two new categoriesWHEREName =N'Other';

Step 2 - Create New Categories

Next, I create two new categories: Parallelism and Backup.

INSERTINTOdbo.PerformanceAnalysisWaitTypeCategory (Name,SortOrder,Enabled,RGBColor)VALUES(N'Parallelism',6,1,'#8B008B'),(N'Backup',5,1,'#FFFF00');

I want to check the value of WaitTypeCategoryID for both, as I'll need them in the next step.

SELECTID,Name

FROMdbo.PerformanceAnalysisWaitTypeCategory

WHEREName IN(N'Parallelism',N'Backup');

Step 3 - Assign Wait Types to new Categories

The final step is updating PerformanceAnalysisWaitType.WaitTypeCategoryID. On my system, the new WaitTypeCategoryID for Parallelism is 7 and Backup is 8. So we can hard-code those values to set CXPACKET to match the Parallelism category's ID value, and the four Backup types to the Backup category's ID value.

UPDATEdbo.PerformanceAnalysisWaitType

SETWaitTypeCategoryID =7WHEREName =N'CXPACKET';UPDATEdbo.PerformanceAnalysisWaitType
SETWaitTypeCategoryID =8WHEREName IN(N'BACKUP',N'BACKUPBUFFER',N'BACKUPIO',N'BACKUPTHREAD');

The updated chart and legend will appear the next time you open the SQL Sentry client, and these new categories will each have their individual tooltip:

SQL Server Waits in History Mode with New Categories

Some final thoughts:

• This feature is Global, so you'll want to apply it to those wait types that are of interest across your entire SQL Server landscape.
• Limit new categories to those wait types most important to you. The chart will become cluttered and difficult to read quickly.
• Use the SQL Server Wait Stats Analysis report to analyze which waits are most prevalent or problematic.