Charting Custom Counters in SQL Sentry
The SentryOne Advisory Condition system delivers advanced capabilities for problem detection, alerting, and response for performance and configuration-related issues across your SQL Server estate. You can create almost any rule you can dream up to evaluate and correlate data collected by SentryOne and take any number of response actions when a condition evaluates as true. An Advisory Condition can include custom SQL queries, any Windows or SQL Server performance counters, and even do basic math calculations against metrics. Check out the Advisory Condition documentation and these blog posts for more details. SentryOne ships with over 100 conditions out-of-the box, with more added regularly, and you can find them all on the SentryOne GitHub repo.
As capable as Advisory Conditions are, until now it hasn’t been possible to track all evaluations for custom counters, only those where the condition evaluated as true. For example, the High tempdb Version Store KB condition runs a custom query against monitored SQL Servers to check for a version store size greater than 200,000 KB. Although this condition and custom counter evaluate every 30 seconds, the only values retained in the SentryOne database are those reported the instant the condition evaluates to true and then when it goes back to false.
The screenshots below show 4 true evaluations of this condition, and I’ve highlighted the second one which lasted for a little over 30 minutes. The first screenshot shows the initial true value (1,320,256), and the second screenshot shows the back-to-false value (768), which is accessed by clicking the End Time cell.
If you want to see the maximum value reached during the 30-minute duration of the event, or all the peaks and valleys in between, you are out of luck. All we know is that the version store size exceeded the explicit threshold of 200,000 KB at 11:43:26 AM.
It works this way because when we first developed this feature, we were concerned that if we logged every result for every condition, it would dramatically increase storage requirements for our database. And this would still hold true today—if we logged every result. With more than 100 Advisory Conditions running as frequently as every 30 seconds (many with multiple embedded rules), if all were enabled in an environment with say, 500 monitored targets, we could easily be saving over 100,000 counter values per minute.
However, we’ve learned from SentryOne users that they don’t necessarily need or want to log every result for every condition. But they do want to be able track results for specific conditions where a higher level of visibility is required, and that’s what these new enhancements are designed to do.
If you’ve applied the SentryOne Scalability Pack and your SentryOne database is running on SQL Server 2017 or newer, you can easily enable enhanced tracking for Advisory Conditions with custom counters using the T-SQL scripts linked below. If you haven’t already changed the DynamicConditionStatus table to memory-optimized, you’ll need to do so first. DynamicConditionStatus is a work table where all Advisory Condition evaluations are temporarily stored in JSON format, and this update adds a natively-compiled trigger on the table to extract the JSON results on the fly and briefly stage them in another in-memory table. From there, they are moved into final counter storage in partitioned clustered columnstore indexes (CCI) every few seconds.
In our testing, enhanced tracking added about 5% CPU overhead to the Advisory Condition tracking process when enabled for 5 conditions. When you consider that a busy SentryOne environment will typically get back around 10% of total CPU by moving DynamicConditionStatus to in-memory, the enhanced tracking overhead comes out to less than 1% of total CPU, and so it’s still a big net gain.
To install the enhanced tracking features, run the following scripts in sequence:
- Change DynamicConditionStatus to memory-optimized
- Install Advisory Condition tracking
- Update the MovePABufferData procedure
Once the scripts have been applied, you can enable enhanced tracking for any SQL Server or Windows condition that includes a SQL Server query against a monitored target, or a user-specified performance counter.
You can do so by executing the SetAdvisoryConditionEvaluationTracking procedure and passing in the condition name, as shown below:
EXEC [dbo].[SetAdvisoryConditionEvaluationTracking] @ConditionName = N'High tempdb Version Store KB' --Advisory Condition name ,@ObjectID = null --(optional) reserved for future use ,@Enabled = 1 --(optional) 1=enable, 0=disable
The procedure will tell you if there are any problems enabling tracking. If successful, the condition will be tracked starting with the next evaluation, and results will be logged to the counter data storage as described above.
NOTE: Tracking will be enabled for all targets to which the condition applies. For example, if you create the condition at the global (All Targets) level, it will be evaluated against all compatible targets monitored by SentryOne.
Use the following code to disable tracking:
exec SetAdvisoryConditionEvaluationTracking N'High tempdb Version Store KB', null, 0;
Once tracking has been enabled, the results are available via the built-in SentryOne Performance Counter History and Performance Counter Date Range Comparison reports.
The Performance Counter History report Parameters screen
The Performance Counter History report showing multiple tracked Advisory Conditions
Although the current implementation should cover many common use cases, there are some limitations:
- The Advisory Condition type must be SQL Server or Windows
- The value retriever type must be SQL Server Query or Performance Counter (User)
- The value retriever must exist on the left side of the comparison operation
- Only the first compatible value retriever in a condition is tracked; all others are ignored
- Only single-instance results are supported
- This includes the Total instance for multi-instance User Performance Counters
- The Any multi-instance evaluator is not supported
These updates will be integrated into the standard SentryOne builds soon but are considered preview until that time, so use at your own risk for now. That said, the practical risks of these changes are minimal, and they should not cause any problems for your SentryOne environment when used properly.
If you upgrade SentryOne before these database updates are integrated, Advisory Condition tracking will be disabled. I recommend saving the script you created to enable tracking and simply run it again after upgrade.
If you are an existing SentryOne user and don’t already have the Scalability Pack but would like to apply it, please reach out to our exceptional Support team for assistance.
I hope you enjoy this powerful new capability! If you have any additional scenarios that you’d like to cover with this feature, please share in the comments below.