Enabling Extended Events

SQL Trace and Extended Events

SQL Sentry XE Trace ensures that Top SQL uses the current SQL trace mechanism. It's also used to collect data while minimizing the overhead and effectively using Extended Events. 

Enable the SQL Sentry XE Trace in Version 2020+

1. Run the following query on the SQL Sentry database to replace SQL trace with extended events:

   --Disable old xe trace, if exists and enabled
   IF EXISTS
   (
   SELECT * FROM dbo.FeatureFlag
   WHERE [Name] = 'XeventsTrace' and [Enabled] = 1
   )
   UPDATE dbo.FeatureFlag
   SET [Enabled] = 0
   WHERE [Name] = 'XeventsTrace';
   --Enable RingBuffer XE trace
   IF NOT EXISTS
   (
   SELECT * FROM dbo.FeatureFlag
   WHERE [Name] = 'XEventsRingBuffer'
   )
   INSERT INTO dbo.FeatureFlag ([Name], [Enabled])
   VALUES ( 'XEventsRingBuffer', 1);
   
   SELECT * FROM dbo.FeatureFlag;

2. Restart the monitoring service.

Enabling the SentryOne XE Trace in Versions 18.4 through 19.x

1. To enable XEvents, add the following row to the [FeatureFlag] table:

   INSERT INTO [FeatureFlag] ([Name], [Enabled]) VALUES ('XEventsTrace', 1)

2. Restart the monitoring service. 

Enabling the SentryOne XE Trace Pre 18.4

Locating the SentryOne Service Configuration File Pre 18.4

The SentryOne monitoring service application configuration file is located in the SentryOne installation directory on the servers hosting the SentryOne monitoring service. Use either of the following files for x64 or x86 SentryOne systems. 

• C:\Program Files\SentryOne\18.0
• C:\Program Files (x86)\SentryOne\18.0

 Edit the configuration file using an editor of your choice. 

Editing the SentryOne Service Configuration File

Add the following query to the appSettings section of the configuration file, and then select Save. 

<add key="xeventsTrace" value="true" />

Complete the previous procedure for all monitoring services that are running in the monitoring solution. The configuration files can be edited while the services are running. However, the monitoring services need to be restarted to pick up the configuration changes.

Verifying Setup

After the services restart, the following behaviors exhibit on the monitored targets:

• SQL Server 2012 and higher uses Extended Events in place of SQL Trace.
• SQL Server 2008R2 and lower remains using SQL Trace.