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.
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.
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.
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.
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
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];
In my experience, the
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.