Manipulating Runtime Thresholds with Object Groups
Devon Leann Wilson
Published On: January 8, 2018
Categories: SQL Sentry, SentryOne 1
One of my favorite things to do as a Solution Engineer at SentryOne is to partner with teams that are running a trial. Getting a glimpse in to other people’s worlds gives me a chance to field questions and explore features in ways that I wouldn’t have thought of on my own.
During one of these recent interactions, the lead DBA pointed out her maintenance and backup jobs on the Event Calendar. She commented that she was getting spammed with e-mail notifications and asked how she could manipulate the Runtime Thresholds for only those jobs. This is where Object Groups come in to play.
Object Groups provide a mechanism to define a special set of settings, conditions, and alerts for groups of objects that share logical commonalities outside of the Global/Site/Group/Instance hierarchical structure found in SentryOne.
Some examples are development, test, or reporting instances that span different sites enterprise-wide. Perhaps there’s a special interest in performance counter alerting on a particular list of databases across the environment.
There are an infinite number of use cases, so for this example, let’s say we want to group up our Log Backup jobs across our environment, and place some special conditions around them. Just imagine having to manually change the Runtime Threshold on every server for every database with Log Backups! Sufficiently depressed? By using the Object Groups feature, applying this kind of behavior is incredibly simple.
First, I’ll take you through setting up an Object Group, then we’ll explore manipulating Runtime Thresholds. If you have any questions, you can always leave a comment below, or request a demo.
Creating an Object Group
Locate the Object Groups icon in the Navigator, and with a right click, open the Object Groups Editor.
Under the Group section, clicking Add will bring up a dialogue box where we can give our Object Group a name and description. In this case, we’ll choose “Selected group members only” since none of these jobs have children associated with them.
Once the shell of group has been created, it will show up in the Groups section. In the case where an object is a member of multiple groups, the group with the highest Evaluation Order supersedes all others and can be changed using the arrows to the right.
Adding Objects to the Group
Clicking Add under the Group Members pane reveals the Select Group Members dialog box where we can search for jobs containing the word “log” and choose which jobs to include in the group.
Defining Object Group Settings
The settings pane provides drop downs that pertain to the types of objects in our group. Because we’re dealing with SQL Agent jobs in this example, we can customize the Runtime Thresholds and Queuing behavior for this group by changing Defined to “True.”
In my original story, my client wanted to widen her Runtime Thresholds. We can do this by setting explicit durations, or use percentages of the job average runtimes, which is what I chose to do here. Before changing the settings on any Object Group, it’s a good habit to double check that our target Object Group is in bold text at the top of the Settings pane as shown below.
We can change the group we’re working on by opening the Navigator and clicking on a different group. As you can see from the ghosted text in the image above, it is possible to do more here. While Job Queuing is outside the scope of this blog post, you can ready more about it in our user guide.
Defining Object Group Alerts
Now that we’ve set up the group and defined our new Runtime Thresholds, we can implement conditions around them.
In the Conditions pane, under the drop down for General Conditions, choosing Add brings up the Actions Selector dialogue box. Here we can choose what kind to action we want to take in response to an issue with this group of jobs.
In this example, I’ve chosen a classic e-mail response. Because our goal here is to curtail the e-mail notifications rather than add to them or disable them, I’ve chosen “Override Inherited Actions” for the action behavior. However, it’s worth noting that you can select Combine with Inherited Actions or Disable as well.
From here, we can customize this e-mail response action further using Condition Settings, and Rulesets, but all of that is covered in more detail in Alec Pickup’s blog on Using the Email Alert Filter.
This was just a quick introduction to Object Groups, but please comment and let us know how you’ve used them in your environment!
Note: This post includes screenshots taken from SentryOne. If you are not yet using SentryOne visit this link for a free trial.
Devon Leann Wilson is a SQL Developer and PASS community volunteer with 7 years of SQL Server experience. She graduated with a degree in Actuarial Science from the University of Texas at Austin and has since worked on business intelligence projects, data governance, and data quality initiatives in the insurance sector. In her free time, she loves karaoke, bravo TV, and staring at puppies on Instagram.