Extended Events in SentryOne

Please note that this blog has been updated to include a link to our 11.2 product release tour.

SQL Server 2008 introduced Extended Events (XE) with the intention of adding a light-weight alternative for SQL Trace. With the release of SQL Server 2012, XE gained parity with SQL Trace in regards to data collection. Microsoft deprecated SQL Trace at this point, advising that new capabilities would be only for Extended Events. However, while the XE capability was, for the large part, very light-weight (there are ways to bring a server to it knees) the framework for consuming these events programmatically was not so great. While being fully featured, the API was not as efficient as it could have been which resulted in performance overheads that we deemed unacceptable. Due to recent overhead improvements, the latest release of SentryOne consumes XE data.

With the release of SentryOne 11.2 we have added XE tracing. It is our intention to continue shipping the SentryOne Platform with SQL Trace as the default capture mechanism - mainly becasue we want to perform more extensive testing. This is a fundamental change in our data collection mechanism that drives numerous areas of the solution. Most notably, Top SQL makes heavy use of the current SQL trace mechanism. As such, the plan is to continue with extensive testing before we flip the switch.

SentryOne and SQL Trace - Some History

There are many misconceptions around SQL Trace, most notably that there is a large overhead. As with anything, if used incorrectly, then yes there can be problems. But the main reason that SQL Trace has such performance problems is due to Profiler, or specifically the way it consumes data. Profiler is incredibly inefficient and this is not helped with poor recommendations. Greg Gonzalez wrote a great blog post "Don't Fear the Trace", explaining the options for more efficient tracing.

One of the areas that Greg covers is Rowset tracing, one of the lowest impact ways to get Trace data from SQL Server. This is the mechanism we use to collect data while minimizing the overhead. However, now there is the option to effectivly use Extended Events.

Enabling SentryOne XE Trace

Currently, the Extended Events trace in SentryOne is off by default. To enable this feature, you must update the service application configuration file. Updating the Appliction Configuration file requires setting a new appSettings flag. The key name is xeventsTrace, and it should be set to true.

Locate the Files

The SentryOne monitoring service application configuration file resides in the SentryOne installation directory. On x64 systems this is C:\Program Files\SentryOne\11.0, on x86 this will be C:\Program Files (x86)\SentryOne\11.0. Edit the SQLSentryServer.exe.config file using your editor of choice. Remember that to edit this file the editor needs to be run as an administrator.

Edit the Files

Once the file is ready for editing, add &ltadd key="xeventsTrace" value="true" /> to the "appSettings" section of the configuration file; then save the file. Complete these steps for all monitoring services that are running in the monitoring solution. You may edit the config files while the services are running. First update all the application config files, then restart all the services. This results in the monitoring services picking up the config changes.

Restart the Services

After the services restart, the following behavior exhibits on monitored targets:

• SQL Server 2012 and higher will now use Extended Events in place of SQL Trace
• SQL Server 2008R2 and lower will remain using SQL Trace

The SentryOne XE trace will now be visible to users under the Sessions node in Object Explorer in SSMS.

The naming convention used for the Extended Events trace is S1_Core_[monitoring service id]_[site id]_[GUID]. This makes it clear where the trace originates for both customers and SentryOne support staff. This naming convention continues for any additional subsequent traces.

Summary

Once a SentryOne administrator makes these changes, the entire monitoring solution uses Extended Events where possible. However, it's important that you remember this feature is still classified as experimental. If for some reason you experience an issue, please contact our support team (Support@SentryOne.com) so that we can assist. Additionally, it is possible to revert to the SQL Trace solution by setting the configuration flag to "false".

Please note that this configuration doesn't persist during an upgrade presently. As such, subsequent upgrades to the SentryOne Platform requires updating the application configuration file.