SentryOne Alerting: Management of Extended Event Sessions

SQL Server Extended Events (XEs) were first introduced in SQL Server 2008 and have slowly become more popular over the years. With Azure SQL Database not supporting SQL Server Profiler and Microsoft’s messaging around the deprecation of Profiler, data professionals are now dealing with XEs a lot more often, and therefore are having to become more familiar with the concept of XEs. Plus, there are more and more events available with each new SQL Server release.

There are several blog posts out there about the benefits of and differences between XEs and SQL Trace, with some even speaking to how “dangerous” SQL Trace is. If you read these blog posts, I recommend also reading one from Greg Gonzalez, “Don’t Fear the Trace,” which tackles some of the misinformation around SQL Trace.

In this blog post, I will cover some ways to monitor the use of XE sessions with SQL Sentry.

Running Extended Event Sessions

The first Advisory Condition (alert) I want to show you how to setup in SentryOne is quite simple—an event to know when an XE session is actively running.

The query shown below will retrieve information about running XE sessions. The Advisory Condition that I will show you how to create later in this blog post will alert on any sessions in which the XERunStatus column has a value of Running. (Note that the Create_time column value in dm_xe_sessions is not null, and three sessions that run on startup are filtered out.)

SELECT ses.name,
CASE
WHEN xe.create_time IS NOT NULL THEN 1
WHEN xe.create_time IS NULL THEN 0
END AS [XERunStatus]
FROM sys.server_event_sessions AS ses
LEFT OUTER JOIN sys.dm_xe_sessions xe ON ses.name = xe.name
WHERE NOT (ses.name='AlwaysOn_health' OR ses.name='system_health' OR ses.name='telemetry_xevents')

For some, this alert might be too noisy, as you will be alerted for every XE session across your monitored environment. Instead of being alerted on every XE session running, you only want to be alerted when certain events are being retrieved. The events I have selected in this example are events that Microsoft warns about because they add performance overhead. The warning message can be seen in the description box when selecting events to capture.

As there are currently only 3 events with the warning in the description box, the query below will return running sessions that are capturing these events. Note that as new releases are made available, there might be more events that contain this warning message.

SELECT s.name, e.event_name 
FROM sys.dm_xe_sessions AS s
INNER JOIN sys.dm_xe_session_events AS e
ON s.address = e.event_session_address
WHERE event_name='query_post_compilation_showplan' OR event_name='query_post_execution_showplan' 
OR event_name='query_pre_execution_showplan'

Note that you can continue taking these alerts further in relation to predicates (filters) not being applied, etc. The main aim here is to just get you thinking of the possibilities when making use of the Extended Events DMVs.

Creating Custom Advisory Conditions

With the queries ready, the last thing you will need to do is set them up in SentryOne. This can be done by either expanding the Advisory Conditions folder in the Navigator pane, double-clicking Conditions List, and then clicking Create Advisory Condition, or by right clicking All Targets and selecting Add Advisory Condition. The screenshot below displays the latter option.

You can create a custom Advisory Condition by following the steps listed here. Again, you will want to keep in mind that, depending on your usage of XEs, some machines might generate many alerts. Applying your conditions to the instances you want to be alerted on is important. This can be done by configuring alerts at specific points in the object and navigation hierarchy—for example, one alert on a server and a different alert on a SQL Server instance running on that server. Patrick Kelley’s “Alert Tuning Basics” blog post walks through examples of how to do so. I hope the two examples above demonstrate the potential possibilities around alerting within SentryOne.

Conclusion

I have included the two conditions we created in the previous section at the bottom of this blog post. Feel free to import these conditions and make use of them in your SentryOne installation. With either of these conditions, or your custom conditions for XE sessions, and the built-in functionality SentryOne provides, you should now be able to correlate running XE sessions to other key metrics collected to ensure performance issues are not occurring as you continue to make use of XEs.

Download the Code Covered in This Blog Post

• XE – Running Session(s).condition
• XE Session – Event Warning.condition

Lee Walsh

Lee is a Customer Success Engineer at SentryOne and assists customers with any questions they have about the SentryOne platform. He originally started as part of SentryOne Support and was one of the first employees hired in the SentryOne Dublin office in April 2018. Since moving to the Customer Success Team, Lee ensures existing customers are getting the most out of SentryOne through tips and tricks, alert optimization, and other feature-related training sessions.