One of the first features that was included in SentryOne also tends to be one of the most under-utilized: SentryOne Event Calendar. It was originally created to provide a visualization of when and how SQL Server Agent Jobs ran. Fast forward a few years, and SentryOne provides a wide variety of additional events that can be viewed on the Event Calendar. The Event Calendar is extremely helpful when you need to identify job scheduling contention, see how multiple events might be related, view differences in events between days, and more.
Table of Contents
Viewing the Event Calendar
Event Calendar Filtering
Using the Event Calendar
Additional Calendar Views
Additional Tricks
Conclusion
Viewing the Event Calendar
The default view of the Event Calendar for a target can be opened by right clicking the target node in the Navigator pane and then clicking Open, Event Calendar. Depending on your user preferences, double clicking the target will also show you the Event Calendar option.
Right-Clicking the Target Workflow to Access the Event Calendar
Double-Clicking the Target Workflow to Access the Event Calendar
The lack of Event Calendar usage isn’t typically due to a lack of awareness of the feature but rather a lack of knowledge of how to manipulate the Calendar view. Some users open the Calendar for a few targets and find it so busy that they can’t really understand what they’re looking at. This often leads to users not really going back to the Event Calendar.
Unfiltered, Default View of the Event Calendar
SQL Server Environments are busier than ever, so SentryOne is often collecting high volumes of data and events. As you can see in the screenshot above, if you don’t filter the information presented in the Calendar, it can be difficult to interpret the data. The good news is, by the time you get through this article, you will be able to create meaningful Calendar views to better understand what events are taking place in your environment.
Event Calendar Filtering
To achieve a view that’s easier to read, you’ll need to do some filtering. You can do so through the Event Calendar pane that’s located near the Navigator pane or by going to View > Event Calendar.
You will want to select a duration filter. Often, a good portion of the workload runs relatively fast and consistently all day long. Rarely are these the types of events you care about when you view the Calendar. I tend to start with a duration filter of 30 to 60 seconds, but the duration will certainly depend on the workload, so play around with this filter to find the ideal threshold. Click Apply Filter at the bottom of the pane to see how the filter affects the view.
Event Calendar Filter Pane
The duration filter helps simplify the view, but you’re likely going to need to do some more filtering. To do so, go to the Event Sources tab at the top of the Event Calendar pane. By default, the event types SentryOne monitors for a target are selected. So, let’s narrow down what you really want to see.
Although the events you want to see can certainly be subjective and situational, a good starting point is to select only Blocking SQL, Deadlocks, SQL Server Agent Jobs, and Top SQL.
If your alerts are tuned, adding Advisory Conditions can also be quite helpful, as they give you additional insight into the state of the target during specific time ranges. Note that if you select SQL Server Agent Jobs as an event source, you will see additional filter options in the scope of jobs. Therefore, you can exclude backups or other jobs based on their categories.
Event Source Filter for Event Calendar
After setting a minimum duration and filtering event types, the calendar should be simplified, but you’re likely not quite there yet. You’re still looking at a full 24 hours by default, and no matter how much reasonable filtering you do, you can only fit so much data in such a small space. Where things really tend to open up at this point is by drilling down to the four-hour or one-hour view. You can do so by highlighting the time range you want to view on the timeline and then double clicking the highlighted range.
Zooming in on a Specific Time Range in the Event Calendar
After reducing the time range, you have a filtered view to gain insight from (as shown in the screenshot below).
Filtered View of Event Calendar
Using the Event Calendar
Now that you have an Event Calendar view that doesn’t make your brain explode, let’s talk about how you can use the Event Calendar efficiently and effectively.
Navigation
Before we dive in, it’s critical to explain how to navigate between the Event Calendar and the Performance Analysis functionality in SentryOne. Although the Event Calendar provides a great visual of various events, it’s not the best for digging into detailed information. A better option is to jump back to the Performance Analysis section of SentryOne.
You can seamlessly jump to various tabs in the Performance Analysis side to view more detailed information by right clicking any event, and then clicking Jump To and the appropriate section (e.g. Dashboard, Top SQL, Blocking). You can also double-click and event to jump directly to that event in Performance Analysis.
Jump To Navigation from Event Calendar to Performance Analysis
Note that the event in question should already be selected in the view you jump to; you don’t even have to dig through the relative time range to find the event. You can also right click an Advisory Condition event in the Event Logs and jump to the Calendar to see what was going on during the duration of an Advisory Condition being active.
The Jump To navigation works the other way as well. In my Top SQL Tips and Tricks article, I discussed how using the grouping feature for the Query History chart could help you find patterns such as a query running longer at the same time every day. Once you have found a pattern like this, you could locate these anomalies in Top SQL > right click the query > Jump To > Calendar. Doing so will take you directly to the time when the query ran on the Event Calendar, which will allow you to see what was going on around that time that might have caused the extended runtime.
Correlation Analysis
The Event Calendar gives you visibility into how different event types are interrelated. So, let’s look at an example so that you can get an idea of how to use the Event Calendar in other situations within your environment.
Let’s say I’m troubleshooting why a query failed. I right click the query in Top SQL and jump to the Calendar. I see the query in question, as well as some other events, including a deadlock and a blocking event. Looking at the query’s Text Data, I see SELECT dbo.Rainfall_rates…. This matches the Text Data of a query being blocked at the same time. Hovering over these queries and viewing the tooltip further verifies they’re related, as they have the same SPID. Quickly jumping to the Blocking tab for this blocking event will verify that the successful UPDATE query to the right of the failed query was, in fact, the head blocker. Furthermore, I see that at the time that the query failed, there was a deadlock event. Jumping to the Deadlocks tab or looking at the tooltip, I verify that the query that failed was the deadlock victim and that the query that caused the original blocking event was also involved in the deadlock.
Event Correlation Analysis
I was able to identify why a query was killed and verify the relationship between these different events. Two queries involved in blocking eventually led to a deadlock, causing the query in question to be killed.
Multi-Day Comparison
When dealing with anomalies in server or event performance, it’s helpful to be able to compare multiple days side-by-side on the Calendar. If you’re having issues today that you didn’t have yesterday, or vice versa, comparing the two days in the Calendar might help you identify the culprit(s). To do so, select a historical day/time and increase the Days count to 2. (Note that although you can view more than 2 days, selecting more than 2-3 days can cause the Calendar view to once again become too busy.)
Multi-Day Calendar View
Now I can easily see if some massive query or job got kicked off that doesn’t typically, if there was blocking, etc.
For example, a SentryOne user who had just learned about the multi-day Calendar view identified that the SQL Server performance issues they experienced around 12pm every Monday was due to someone kicking off a massive report prior to lunch in hopes it would be completed by the time they got back. As is often the case, the person running the report hadn’t considered the impact that running such a report would have in the middle of a busy time range.
Other Calendar Views
Sometimes, it’s easier to dig into event information through the List view. This view will turn the visual into more of a report. In addition, while in the List view, you can go to File > Export and export the data to Excel for sharing or storing. Note that the columns and their order at the top of the List view change how the result set is grouped and displayed.
Event Calendar List View
There’s also the Split view, which you might find helpful. While in the Split view, clicking an event in one section will auto-highlight the same event in the other section. So, once you identify an event of interest in the Calendar, you don’t need to go digging for it in the List view.
Additional Calendar Views
So far, we have looked at the default Calendar view for a single target, that shows everything, and filtering down to what we need. But there are also event-specific views and custom event views.
Event-Specific Views
If you expand the Instance node of a target in the Navigator pane, you will find Top SQL, Blocking, and Deadlock nodes. These nodes are pre-filtered for those specific event types. If you want to broadly compare blocking today versus yesterday, you can start here rather than heavily filtering the default Calendar view. You can also drill down to specific jobs to view just a jobs history in the Calendar view.
Event-Specific Calendar Views
While we’re looking at this section, it’s important to mention the Runtime Stats node under each job (shown in the screenshot above). Opening Runtime Stats will give you a visual representation of your jobs’ durations over time (as shown in the screenshot below).
Agent Job Runtime Stats Chart
Custom Event Views
Unless you have only a single SQL Server to monitor, you’ll need a broader view of your environment. It isn’t typically feasible to be able to look at all your targets every day. However, you can use Custom Event Views to create a Calendar view that shows the events you care about across all or a subset of your targets.
Some common examples would be a view that shows all full backup job failures, or Tier 1 and Tier 2 job failures, across all production servers in a single view.
Custom Event View Node
I have also seen similar views that are filtered to show only Open events. This means you could create a view that allows you to close out events and remove them from the view, creating a sort of work item list.
When creating a Custom Event View, you can create it under Local or Shared. Views created under Local will be visible to only the user that created them. Views created under Shared can be seen by any SentryOne user. Note that any users with restricted visibility through Rights Based Security will be unable to open Custom Event Views unless they have explicit access to all related targets in the view. To create a new view, right click Shared/Local > New.
There are several different ways to create a desired view, and several nuances to consider when creating a view. For instructions on creating a Custom Event View, check out the Custom Events View tutorial in SentryOne Docs.
Additional Tricks
Event Notes
You can add notes to a specific event or like events. These notes could include anything from resolution steps, to troubleshooting completed so far, to a reminder to ignore an event. The ability to add notes can be quite helpful when working with geographically dispersed teams who aren’t always able to communicate directly with one another due to time differences. You can include the notes in alerts related to the object in question, broadly add them to the object, or add them to the occurrence in question. Notes will be logged with a timestamp and who logged it for easy auditing. To add a note, right click any event and then click Add/Edit Notes.
Add/Edit Note for Blocking Event
User Preferences
There are several settings you can change related to the Event Calendar through User Preferences (Tools > User Preferences). The two main tabs in User Preferences related to the Event Calendar are the Calendar and Color tabs. If you prefer certain colors on your Calendar, you can change the colors to what works best for you.
There are several settings that you can adjust in the Calendar tab, but an often-changed settings is the Show Events as Conflicts After. By default, events that overlap for more than 5 minutes will be highlighted in orange. This default can be a bit aggressive in some heavy workload environments, so you can change this setting as needed. Ultimately, the goal of the highlighting is to show time ranges that might be over scheduled.
Calendar-Related User Preference Settings
Like Event Highlighting
If you click an event, like events are set to a darker shade. This helps you to see the trees you want through the forest. The criteria for what a like event is can be changed by right clicking an event type and selecting Highlighting Options.
Navigating to Highlighting Options
Selecting Text Data is one of the more commonly used options. But choosing options such as Login or Application could also be helpful in certain situations.
Like Event Highlighting Criteria Options
Conclusion
I hope you have found some new and useful ways to leverage the Event Calendar in SentryOne. Whether it’s a deep dive view of one target or a broad view across multiple targets, the Event Calendar can provide a unique view of your events, providing insight into your workload and the relationship between multiple event types. If you have any questions about or issues with the Event Calendar, please reach out to SentryOne Support or your Customer Success Manager.
If you haven’t given SentryOne a try yet to explore Event Calendar further, you can download a 14-day trial here.
