Custom Conditions: Trigger Threshold
Published On: September 6, 2016
Categories: SQL Sentry, Advisory Conditions, Custom Conditions 0
Custom Conditions provide an option called Trigger Threshold. I sometimes see conditions created with this option when it isn't necessary, so I'm going to demonstrate how it works.
Figure 1: Trigger Threshold Option for Custom Conditions
The Trigger Threshold defines the amount of time a condition must be true before a linked action performs. This option is set to 30 seconds by default when creating a new condition; however, depending on the purpose of your custom condition, you may wish to change this value.
For example, checking a configuration or the value of a setting probably doesn't require it to be true for a specified amount of time before springing into action. In the published conditions, you'll see conditions that run relatively infrequently (e.g. hourly, daily, or weekly) when evaluating these situations.
One such example is the 'High Performance' Power Plan not Enabled condition. This condition examines the value of the Windows Power Plan every 6 hours. It doesn't necessitate maintaining a certain value for a minimum amount of time to be true, therefore the Trigger Threshold is set to 0 seconds.
Figure 2: Trigger Threshold Set to Zero
When to Set a Trigger Threshold
The High CPU condition (shown below) is a great example of when to set a Trigger Threshold.
Figure 3: Trigger Threshold Set to One Minute
In this performance-based evaluation an associated action isn't desired as soon as the CPU spikes above 90, therefore the threshold is set to one minute to trigger actions only when that >90 value is sustained for at least one minute.
Nothing to Do Here
For the sake making a quick easy-bake example (not to be confused with the painstakingly slow method of baking with a light bulb), I've created a test condition to look for CPU > 20%. Notice that the threshold is set to 30 seconds, as is the Default Evaluation Frequency. Quick side-note, it's a good idea to set the frequency time <= the threshold time.
Figure 4: The Test Condition
To begin, there's an evaluation that results in a 'True' at 3:01 PM.
Figure 5: Condition Evaluated to True
Notice on the Performance Advisor Dashboard that the 'True' result was part of a spike that did not last 30 seconds.
Figure 6: CPU Chart on Performance Advisor
As a result, even though the condition evaluated to 'True', when I check the Events Log (shown below) nothing is logged here for 3:01 PM because the threshold trigger was not satisfied.
Figure 7: Show Events Log
Only some previous events from earlier today are showing in this filtered view:
Figure 8: Events Log
The Trigger Threshold in Action
For my next trick, I'll cause the CPU to exceed 20% for more than 30 seconds.
Once the CPU has exceeded 20% for the threshold period during an evaluation, it will appear "Active" in the Events Log.
Figure 9: Event Logging in Progress
After the condition is no longer met, the logging of the event is completed with a stop time.
Figure 10: Event Captured
As can be seen, the red highlight on the Performance Advisor Dashboard doesn't include the first 30 seconds while the threshold was being met. It then continues past the point where the CPU dropped below 20% as it completed the evaluation cycle (also set to 30 seconds). That evaluation stated off as true and was logged as such. The following evaluation is false and the highlighting/logging ends.
Figure 11: Performance Advisor Dashboard Highlight
The Trigger Threshold is a smart option available in Custom Conditions that allows highly configurable alerts and actions. Hopefully my example has left you well-equipped to know when and how to use it when creating conditions. If you are new to Custom Conditions or simply need a refresher, please check out one of my older posts, Custom Conditions : A Logical Choice.
Melissa is the Product Education Manager at SentryOne. Melissa has over a decade of experience with SQL Server through software performance and scalability testing, analysis and research projects, application development, and technical support.