Those of you who have experienced SQL Sentry Performance Advisor's amazing feature set expansion over the last couple of years are already aware of two of the biggest features delivered in version 8 - Performance Alerting and Fast Query Capture. If you are new to SQL Sentry, or haven't delved into these additions, you can get acquainted with them by reading two previous blog posts by Greg Gonzalez (b | t): Intelligent Alerting Redefined, and Adaptive Web UIs and Fast Query Capture.
In version 8.3, we have pushed the envelope again, enhancing both features to make them even more useful. Arguably the most exciting enhancements in v8.3 are found in Custom Conditions, where three important new features make their appearance. Additionally, the Show Totals feature in Performance Advisor's Top SQL module has been extended to the Procedure Stats and Query Stats tabs. We'll touch on each of these features in this post and, as always, more in-depth information is available in the User Guide.
Download SQL Sentry v8.3 here: New Users | Existing Users
Custom Conditions
Intelligent performance alerting has been an instant hit with our customers since its debut in version 8.0. The power and flexibility of this feature are being creatively exploited in a wide-ranging fashion, leading to numerous feature requests and innovative ideas. Version 8.3 implements three of the best:
User Performance Counters
An important feature of Custom Conditions is providing users with the flexibility to build highly customizable and potentially complex performance counter threshold conditions that have minimal impact on monitored instances. Until now, Performance Advisor has achieved that goal by restricting the available performance counters to those already collected and available within the SQL Sentry database.
In v8.3 we have lifted that restriction to expose any and all performance counters on a monitored instance for use in Custom Conditions. That includes performance counters installed by software other than SQL Server and Windows such as SharePoint, Hyper-V and third party products!
Creating a Custom Condition with a User Performance Counter
The interface is the same as in earlier releases, so start by clicking the Create Custom Condition button from a Conditions List, and selecting the appropriate sub-item. Next, select the data type of the first comparison (numeric, string, or date/time), then the Performance Counter (User) value source type:
Choosing any performance counter
Now, tell SQL Sentry what counter you want to use by connecting to a server and exposing a list of performance counter categories and counters. Do that by clicking on the Connect button, enter a server name, then click Connect again. After connecting to the server, select the Category field to view a list of all the performance counter categories on that server.
Choosing a performance counter category (click to enlarge)
After selecting a category, select the counter you want to evaluate.
Choosing a specific performance counter (click to enlarge)
From here you complete the definition of the Custom Condition just like any other by working through the elements exposed in the editor.
Of course you will also want to review and possibly alter options such as Default Evaluation Frequency, Severity, etc. See the Custom Conditions topic in the User Guide for details. Finally, give your new Custom Condition a name and description, and save it. Our completed example is below:
Custom Condition using a user-selected performance counter (click to enlarge)
A Point to Ponder
As in life, with great power comes great responsibility. Custom Conditions that use User Performance Counters are very powerful, however they are evaluated directly against your monitored servers, so they have the potential for creating a negative impact in your environment if used indiscriminately. Keep the potential impact in mind as you choose evaluation frequencies, the number of monitored servers to be evaluated, and the number of these conditions that you implement. Use Performance Advisor to look for any measurable changes in performance on your monitored servers resulting from your new custom conditions.
Custom Condition Event Sources
Until this release, visibility of Custom Conditions that evaluate to true was limited to the Performance Advisor dashboard, the Events Log, or the Server Health Status screen in cloud.sentryone.com. Granted, if you have seen triggered custom conditions on the PA dashboard, you know that really wasn't much of a limitation, as they provide a wealth of contextually relevant information. Still, we wanted to further expand the visibility of Custom Conditions as well as their effectiveness in troubleshooting.
To that end, SQL Server and Windows Custom conditions have been integrated into the Event Manager Calendar as new Event Sources, just like Top SQL, Blocking SQL, and Deadlocks.
Enable this feature from the Event View, Event Sources tab of any Event Manager calendar window. When you view this tab from an Event Manager calendar at the Global level, for a Computer Group, or for a Computer, it looks like this:
Enable Custom Conditions on the calendar
When you view this tab from an Event Manager calendar for a specific Windows or SQL Server connection, your options change a bit. At the connection level, this tab includes either Custom Conditions: Windows or Custom Conditions: SQL Server (depending on the connection type), and you are presented with an Event Objects list of the specific custom conditions enabled for that connection. You can select one, multiple or all of these Custom Conditions to appear on your calendar.
Display specific Custom Conditions
Once enabled, SQL Server and Windows custom conditions such as High CPU will appear on the Calendar alongside all other events like jobs and Top SQL, enabling you to easily ascertain causality for the condition.
Custom Conditions appearing on the Event Manager calendar (click to enlarge)
Of course this also means you can create Custom Event Views that display all or a subset of these custom conditions for one, several, or all your monitored instances.
Returning Multiple Results
A very common use for Custom Conditions is to send an alert when the amount of free space on a disk volume falls below a defined threshold such as 10% or 20%. For most environments the recommended approach is to use a Repository Query that returns values from PerformanceAnalysisDeviceLogicalDisk.Freespace and compares them to a defined threshold. This is a great solution and SQL Sentry correctly alerted whenever a value was discovered that exceeded the defined threshold. The Evaluation Result pane looked something like this:
Custom Condition evaluation showing a single result (click to enlarge)
However, there is a possibility that more than one volume might exceed the defined threshold at any point in time. It would be even more useful to display all volumes that exceed the threshold so, in v8.3, this condition will return and display multiple rows (up to 100). This is a user-defined setting found in the Options section of the Custom Condition definition tab:
Maximum Instance Count setting
Now, when you hover over the returned value you see the full result set:
Custom Condition evaluation showing multiple results (click to enlarge)
It's important to note that this feature works the same for any action that displays results, such as Send Email and Log to Database. Here is an example of the resulting message in the Actions Log:
Log message showing all affected servers/drives (click to enlarge)
So you can be sure your alerts will contain all of the same relevant information.
Grouped Mode for Procedure Stats and Query Stats
In v8.2 the Procedure Stats and Query Stats grids showed each captured query once, with cumulative statistics over the active date range. That’s great information, but we thought it would be even better to have the choice of viewing execution counts and statistics for each sample period (Details Mode):
Details Mode - show aggregates per sample period (click to enlarge)
Or the cumulative statistics for all executions over the active range plus a drill-down capability showing the statistical breakdown per sample period (Totals Mode):
Totals Mode - drill-down per sample period (click to enlarge)
This new functionality eliminates any perceived advantage in setting the Top SQL: Minimum Duration Threshold value to a very low number - such as less than 1 second. In fact a good argument can be made that, except in certain edge cases, the default 5 second minimum duration threshold should always be used once fast query capture has been enabled.
To ensure you are making the best use of this feature, read Greg's blog post, as well as the pertinent section of the Top SQL topic in our User Guide. There are settings specific to this feature that you need to become familiar with, and possibly tune, in order to capture performance-impacting, short duration queries.
Bonus: An additional new feature of these grids is column persistence across sessions. To see this for yourself, open the Column Chooser and add or remove columns from the grid (or just rearrange them). Close and re-open your PA session and the same columns will be present in the same order you left them.
But wait, there's more!
This has been a long post, and I haven't mentioned other new features in this release, including two reports, a Disk Space Usage Band treemap for indexes, and more. Get the full list from the current Change List, and as always check the User Guide for a complete description. You can also stay on top of new releases by bookmarking the "latest builds" post on our team blog.
Facebook
Linkedin
Twitter
Comments