In my last post I covered the exciting new intelligent alerting features in SQL Sentry v8. Next up in the series is another huge and often requested feature:
Baselining
Designing this feature proved to be somewhat of a challenge due to the diversity of opinions on this topic. It seems that everyone has a different idea about baselining – what it is, and how it should be used effectively to improve performance. So, as is often the case, we tried to look beyond the term and assess what people truly need to accomplish with this feature. We knew that what we built had to satisfy a variety of use cases, be simple to use, and integrate seamlessly with the rest of our software.
To pull it off, we’ve leveraged both pre-existing and new SQL Sentry functionality to produce what I believe is the most comprehensive and intuitive baselining for SQL Server and Windows to date. With the new Performance Advisor baselining you can:
- Compare the current date range against built-in temporal baselines to quickly ascertain patterns in counter behavior
- Create baselines for performance metrics using source data from any date range
- Create any number of baselines with friendly names and easily switch between them
- Send alerts or take various other actions in response to deviations from baseline values
Built-in Baselines
On the Performance Advisor Dashboard you’ll see a new Baseline dropdown in the upper right. It is pre-populated with several temporal baselines:
Select one of these baselines and you’ll see a moving line or min/max range overlaid on top of the current charts:
(click to enlarge)
In the shot above I’ve selected Previous Day with a Range style overlay. You can see patterns emerge on several charts, and it turns out that the spikes in waits, CPU, etc., appear to be normal behavior for that time of day. Of course, normal doesn’t necessarily mean acceptable. 😉 Fortunately, PA lets you ascertain the root cause of most any spike, whether from SQL Server or some other Windows process, so you quickly can make a determination about acceptability.
Note that the built-in baselines listed will change depending on the size of the active date range. For example, if you are in a 3-day range, Previous Day doesn’t make sense, so you won’t see it. Also, the Range option is currently only available when viewing detail data (range <= 30 minutes).
Custom Baselines
In addition to the built-in baselines, you can create your own custom baselines! To do so, simply click and drag on any chart to highlight a date range, then select the Create Baseline context menu. Here I’ll create a baseline called “Business Hours” which represents normal and acceptable performance during that time:
Next, a screen appears which shows the Average, Min, Max and Standard Deviation for each dashboard metric over the selected range. From there you can choose which metric is shown by default on multi-metric charts, as well as manually adjust the baseline value by editing the Average column:
(click to enlarge)
Once saved, the baseline is added to the Baseline dropdown as selected, and a horizontal line appears on each chart showing the baseline value for each metric. The line provides a reference point so you can easily spot unusual activity. Here we see that at around 10:10AM something started impacting disk waits, CPU, PLE, and page reads, causing them to deviate from the normal business hours level:
(click to enlarge)
From here you can highlight the range and Jump To other areas like Top SQL, Processes or the Event Manager Calendar to get at the root cause.
There are many use cases like this for custom baselines. Here are a few others:
- Create a baseline prior to making a configuration change on the server, then use it to immediately monitor how the change impacts performance.
- Create a baseline for intensive maintenance operations like integrity checks, index rebuilds, or backups, and periodically review the operation against its baseline to ensure it is still performing as expected. I’ve seen many times where a change to the network or the SAN by some other group can have a dramatic impact on maintenance ops, and this is a great way to catch this!
- Create periodic date-based baselines, such as “Jan 2014”, “Feb 2014”, etc., and use them to assess how performance is changing over time.
You can create as many custom baselines as you want, and edit them at any time by clicking the build button to the right of the dropdown. For multi-metric charts, you can change the default metric shown without editing the baseline by right-clicking it in the legend.
Baselines and Alerting
These visualization capabilities are great, but the feature wouldn’t be complete without the ability to generate alerts using baseline data. That’s where the other new SQL Sentry functionality comes in: when a custom baseline is created at the Windows computer or SQL Server level, it becomes available for use in custom conditions at that level.
Using baselines in a custom condition is easy – instead of setting an explicit threshold value on the right side of a comparison, select Performance Counter Baseline, and its value for the metric is automatically substituted. This way if a baseline value ever changes, you won’t need to update any conditions.
In the condition below, for demonstration purposes I’ve added 3 separate baseline tests with some basic math to control how large of a deviation from the baseline value is required to trigger the condition:
(click to enlarge)
The system knows to use the baseline value for the counter on the left side of the comparison. Early versions required full declaration of the counter on the right, but this took too long to configure and made the comparisons far too wide, so we came up with this context-aware shorthand.
You can select a baseline on the left side, but you’ll forego the shorthand when doing so. It generally reads easier to have the counter on the left and threshold on the right anyway.
Baselines vNext
As you can see, we’re doing quite a bit with baselines out of the gate… but as I said in my last post on v8, we are just getting started! We’re already working on some very cool enhancements to make baselines even more useful. In the meantime, I hope you take advantage of these new capabilities, and as always, your feedback is appreciated.
Download SQL Sentry v8 here: New Users | Existing Users
