My Favorite SQL Sentry v8.4 Features: #1 – Dashboard Custom Wait Categories

We just released version 8.4 and it is brimming with new and enhanced features. I have been working my way through the Features section of the Change List, exercising the features that I think will have the most impact on our Professional Services customers.

Download SQL Sentry v8.4 here: New Users | Existing Users

Before starting this process, I expected to select one or two favorites to share with everyone who reads my blog (yes, all three of you). The problem is, I was only able to narrow my list down to seven features. OK, actually eight. And the first one requires a post of its own. So, I now have material for a series of posts. Thank you SQL Sentry Development Team!

The SQL Server Waits Chart

Up until this release, 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

Cool New Feature #1 – Display Custom Wait Type Categories on the Dashboard

So, what does version 8.4 add to the Wait Stats chart? The ability to define new categories, assign specific wait types to those categories, and have them appear on the chart in History Mode. To my knowledge, this may be the most requested Performance Advisor Dashboard feature.

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. Jason Hall’s (b | t) post Mining Performance Data from SQL Sentry : Part 5 examines these tables in more depth and explains how to use them for troubleshooting performance issues.

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.

UPDATE dbo.PerformanceAnalysisWaitTypeCategory

  SET SortOrder += 2 -- make room for two new categories

  WHERE Name = N'Other';

Step 2 – Create New Categories

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

INSERT INTO dbo.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.

SELECT ID, Name

  FROM dbo.PerformanceAnalysisWaitTypeCategory

  WHERE Name 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.

UPDATE dbo.PerformanceAnalysisWaitType

  SET WaitTypeCategoryID = 7

  WHERE Name = N'CXPACKET';
UPDATE dbo.PerformanceAnalysisWaitType
SET WaitTypeCategoryID = 8
WHERE Name 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 own individual tooltip:

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.

In my next post, I will examine several more new features in version 8.4. I hope you will join me.

Justin Randall

Justin (@jh_randall) leads our Professional Services Organization with the perspective of nearly 30 years of IT experience. Professional Services provides customers with mentoring, consulting and training services focused on configuring and tuning SentryOne software, and SQL Server performance troubleshooting and optimization.

Justin’s blog focuses on helping our users become highly valued DBAs who use our software to prevent and solve real-world SQL Server problems while making positive contributions to their organization’s success.