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