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.
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.