Extended Events was introduced as a replacement for SQL Trace way back in SQL Server 2008. However, the initial implementation offered no UI for users, making it difficult for some to adopt this new method of event capture. SQL Server 2012 put that right, with a UI fully integrated into SSMS, which also meant that we no longer needed to learn XQuery in order to interrogate the XML event data. As a result, database administrators and developers have started to make the leap from Trace and Profiler over to Extended Events.

Over the course of this stairway series, we're going to explore in detail the use of Extended Events as a diagnostic data collection tool, to track down causes of poor performance in SQL Server. This first level will start from a point known and familiar to many DBAs, namely the use of SQL Trace to track down and investigate long-running queries. From that base camp, I'll show how to perform the same task in a new way, using an event session in Extended Events.

Extended Events is much more than a straight replacement for SQL Trace and Profiler. As you progress through the series, you'll see how diagnostic techniques that would be prohibitively high-overhead with SQL Trace become viable with Extended Events, and previously difficult, or even impossible, tracing tasks become not only possible but also much simpler, with Extended Events.

SQL Trace and Profiler are no longer being enhanced, and while they are tools that so many of us have known for years, Profiler was released with SQL Server 7.0 in 1998, it is finally time to embrace Extended Events and understand all that it has to offer.

What is Extended Events?

Extended Events is a new event collection infrastructure, first introduced in SQL Server 2008. We can use Extended Events to collect and analyze diagnostic data for many different types of events that occur within our SQL Server instances and databases. Extended Events is part of SQLOS, and is composed of multiple modules that are loaded when SQL Server starts. It offers a vast array of events that replicate, improve and extend on what is available in SQL Trace.

When Microsoft decided to replace SQL Trace with Extended Events, they started from scratch, and designed an entirely event collection architecture. One of their goals was an architecture that would be highly extensible, allowing them to add new events easily, as required. As Microsoft has introduced new features to SQL Server, such as Availability Groups, In-Memory OLTP, and Columnstore indexes, so it has added to Extended Events specific events with which to collect diagnostic troubleshooting data when using these features. For systems running SQL Server 2012 or higher, adoption of Extended Events is crucial, as events for these new features are found only in Extended Events.

Table 1 lists the number of events that Extended Events makes available in each major SQL Server release. These figures are for all events, including debug events. In all listed versions, SQL Trace offers only 180 events. In SQL Server 2012, all events which exist in SQL Trace have a comparable event in Extended Events, though there is not a strict one-to-one mapping, as we'll see later.

Table 1: Number of events provided by Extended Events for each SQL Server release

An equally important design goal for Extended Events was to make event data collection as low impact as possible, in terms of additional resource overhead generated on the server under investigation. Extended Events achieves this in a number of ways, all of which we'll discuss in more detail in later levels:

• A minimal 'default payload' for each event – by default each even collects only a minimal number of event data columns. If we wish to collect further columns we must explicitly add them to the event as actions. By contract, SQL Trace would collect a large default payload and then we would simply disregard any unrequired data columns.
• Powerful predicates for early filtering – Extended events provides fine-grained filtering, via predicates, so that we collect data only when an event occurrence meets certain criteria. We can use predicates to collect only specific event occurrences, such as every fifth occurrence, or collect the event only under specific circumstances, such as when a data column value (e.g. Duration) exceeds a previous maximum value. Extended Events predicates are applied very early in the event firing process, as soon as the default payload is collected, to avoid any unnecessary data collection overhead.
• Advanced targets – as well as basic in-memory (ring_buffer) and file system (event_file) targets, similar to what was available in SQL Trace, Extended events offers advanced targets that "pre-aggregate" data according to certain criteria.

All of this means that, even if we define relatively complex event sessions that require data from numerous events, we can with careful design of predicates and choice of target collect the data need with minimal overhead on the observed server.

In short, the vast number of events, combined with improved filtering options and multiple options for data collection, make Extended Events a far superior option for event collection when compared to Trace.

Starting from what you know: SQL Trace

In my experience, one of the easiest ways to learn something new is to build on what you already know. This introduction uses what you already know about SQL Trace and Profiler as a foundation from which to build an understanding of how Extended Events work, and how they are different from Trace and Profiler.

A common task for many data professionals is to troubleshoot poorly performing SQL queries. Traditionally, we would create a trace to capture event data related to the execution of our stored procedures and queries. We would set a trace filter to capture only those queries that that exceeded a certain number of reads or certain duration, or consumed the most CPU.

A typical first step is to open up Profiler as a convenient way to define the trace. We open a new trace, or perhaps use an existing trace template, connect to an instance, then select the required events. In Figure 1, we have selected only two events, RPC:Completed and SQL:StmtCompleted, capturing the same data columns for each event.

Figure 1: Selecting events and data columns for a trace

When using Profiler or SQL Trace, it is always recommended to add a filter. One of the problems with SQL Trace, which Extended Events addresses (see later), is that it performs late filtering. When we define a filter, SQL Trace or Profiler still collects event data for every instance of an event, and then applies the filter. Of course, the filter is still vital as it ensures that we send to the client or file target only the event instances that match the filter condition.

In this case, we'll filter on reads greater than or equal to 10000, as shown in Figure 2.

Figure 2: Defining a trace filter on number of reads

With the events and columns selected, filters added, and trace file information set up, you're ready to start the trace. Seasoned DBAs and developers know that running Profiler introduces significant performance overhead (see http://support.microsoft.com/kb/929728 for further details). Therefore, to minimize the impact on the production environment, a best practice followed by many is to start and immediately stop the trace and then script out the trace definition via File | Export | Script Trace Definition | For SQL Server 2005 – 2014… so that queries can be captured via a server-side trace instead of the GUI. Listing 1 shows the resulting script.

/****************************************************//* Created by: SQL Server 2014 Profiler          *//* Date: 11/30/2015  08:50:44 AM         *//****************************************************/-- Create a Queue
DECLARE @rc INT
DECLARE @TraceID INT
DECLARE @maxfilesize BIGINT
SET @maxfilesize = 5 
-- Please replace the text InsertFileNameHere, with an appropriate
-- filename prefixed by a path, e.g., c:\MyFolder\MyTrace. The .trc extension
-- will be appended to the filename automatically. If you are writing from
-- remote server to local drive, please use UNC path and make sure server has
-- write access to your network share
EXEC @rc = sp_trace_create @TraceID OUTPUT, 0, N'InsertFileNameHere',
    @maxfilesize, NULL 
IF ( @rc != 0 )
    GOTO error
-- Client side File and Table cannot be scripted
-- Set the events
DECLARE @on BIT
SET @on = 1
EXEC sp_trace_setevent @TraceID, 10, 10, @on
EXEC sp_trace_setevent @TraceID, 10, 3, @on
EXEC sp_trace_setevent @TraceID, 10, 12, @on
EXEC sp_trace_setevent @TraceID, 10, 13, @on
EXEC sp_trace_setevent @TraceID, 10, 14, @on
EXEC sp_trace_setevent @TraceID, 10, 15, @on
EXEC sp_trace_setevent @TraceID, 10, 16, @on
EXEC sp_trace_setevent @TraceID, 10, 18, @on
EXEC sp_trace_setevent @TraceID, 10, 26, @on
EXEC sp_trace_setevent @TraceID, 41, 3, @on
EXEC sp_trace_setevent @TraceID, 41, 10, @on
EXEC sp_trace_setevent @TraceID, 41, 12, @on
EXEC sp_trace_setevent @TraceID, 41, 13, @on
EXEC sp_trace_setevent @TraceID, 41, 14, @on
EXEC sp_trace_setevent @TraceID, 41, 15, @on
EXEC sp_trace_setevent @TraceID, 41, 16, @on
EXEC sp_trace_setevent @TraceID, 41, 18, @on
EXEC sp_trace_setevent @TraceID, 41, 26, @on
EXEC sp_trace_setevent @TraceID, 41, 61, @on

-- Set the Filters
DECLARE @intfilter INT
DECLARE @bigintfilter BIGINT
EXEC sp_trace_setfilter @TraceID, 10, 0, 7,
    N'SQL Server Profiler - f45d52c9-c0eb-45da-8bae-dc6f1a945251'
SET @bigintfilter = 10000
EXEC sp_trace_setfilter @TraceID, 16, 0, 4, @bigintfilter
-- Set the trace status to start
EXEC sp_trace_setstatus @TraceID, 1
-- display trace id for future references
SELECT  TraceID = @TraceID
GOTO finish
error: 
SELECT  ErrorCode = @rc
finish: 
go

Listing 1: A server-side trace to capture poorly-performing queries

Some of you may have stepped through a SQL Trace script before, and thus have familiarity with the different functions it contains. However, to make sure we're all starting from the same base, I'll provide a quick review here.

The initial section sets the necessary variables to be used in the sp_trace_create function, which create the trace definition. As a user, you specify the maximum file size (@maxfilesize in this example, set to 5 by default when you script out the trace). You can also specify other options such as whether the trace will rollover, and if so the number of rollover files to create. See http://msdn.microsoft.com/en-us/library/ms190362.aspx for more details on sp_trace_create.

The path for the output file is also set as part of sp_trace_create. Before running this trace, replace InsertFileNameHere with the appropriate file location, such as C:\temp\ReadsFilter_Trace. As noted in the comments of the script, you should not include .trc in the file name.

-- Create a Queue
DECLARE @rc INT
DECLARE @TraceID INT
DECLARE @maxfilesize BIGINT
SET @maxfilesize = 5 
-- Please replace the text InsertFileNameHere, with an appropriate
-- filename prefixed by a path, e.g., c:\MyFolder\MyTrace. The .trc extension
-- will be appended to the filename automatically. If you are writing from
-- remote server to local drive, please use UNC path and make sure server has
-- write access to your network share
EXEC @rc = sp_trace_create @TraceID OUTPUT, 0, N'InsertFileNameHere',
    @maxfilesize, NULL 
IF ( @rc != 0 )
    GOTO error

Listing 2: The sp_trace_create portion of the server-side trace

As defined, this trace would run until we stop it, manually. Alternatively, we could supply a @DateTime parameter to sp_trace_create, so that we can specify that the trace to run only for a limited duration (for example, using something like set @DateTime = dateadd(hh, 1, getdate()) to run the trace for an hour).

The next section of the trace sets up the required events. The sp_trace_setevent function adds every combination of event and column that you want to capture. Reading this output is not intuitive, as integers are used to identify the events and columns. It's quite common to reference Books Online (http://msdn.microsoft.com/en-us/library/ms186265.aspx) to lookup the values for each. In the code below, the integers 10 and 41 (seen immediately after @TraceID) are for the RPC:Completed and SQL:StmtCompleted events, respectively. The second integer represents the data column. For example, 10 is ApplicationName, 3 is DatabaseID, and so on. I edited the original code to include comments, for clarity.

-- Set the events
declare @on bit
set @on = 1
exec sp_trace_setevent @TraceID, 10, 10, @on    --RPC:Completed, AppName
exec sp_trace_setevent @TraceID, 10, 3,  @on    --RPC:Completed, DatabaseID
exec sp_trace_setevent @TraceID, 10, 12, @on    --RPC:Completed, SPID
exec sp_trace_setevent @TraceID, 10, 13, @on    --RPC:Completed, Duration
exec sp_trace_setevent @TraceID, 10, 14, @on    --RPC:Completed, StartTime
exec sp_trace_setevent @TraceID, 10, 15, @on    --RPC:Completed, EndTime
exec sp_trace_setevent @TraceID, 10, 16, @on    --RPC:Completed, Reads
exec sp_trace_setevent @TraceID, 10, 18, @on    --RPC:Completed, CPU
exec sp_trace_setevent @TraceID, 10, 26, @on    --RPC:Completed, ServerName
exec sp_trace_setevent @TraceID, 41, 3,  @on    --SQL:StmtCompleted, DatabaseID
exec sp_trace_setevent @TraceID, 41, 10, @on    --SQL:StmtCompleted, AppName
exec sp_trace_setevent @TraceID, 41, 12, @on    --SQL:StmtCompleted, SPID
exec sp_trace_setevent @TraceID, 41, 13, @on    --SQL:StmtCompleted, Duration
exec sp_trace_setevent @TraceID, 41, 14, @on    --SQL:StmtCompleted, StartTime
exec sp_trace_setevent @TraceID, 41, 15, @on    --SQL:StmtCompleted, EndTime
exec sp_trace_setevent @TraceID, 41, 16, @on    --SQL:StmtCompleted, Reads
exec sp_trace_setevent @TraceID, 41, 18, @on    --SQL:StmtCompleted, CPU
exec sp_trace_setevent @TraceID, 41, 26, @on    --SQL:StmtCompleted, ServerName
exec sp_trace_setevent @TraceID, 41, 61, @on    --SQL:StmtCompleted, Offset

Listing 3: Setting the trace events

Within the database engine, the trace controller checks to see if an event is being captured (if it's part of a defined trace). If so, it captures the information for that event and then pushes it to either the rowset provider, if you're running Profiler, or to a file. Before it does this, it removes any of the columns that were not selected. For example, for our two events, we didn't select the DatabaseName column. However, SQL Server still captures the DatabaseName data column for both events, by default, but then does not send it to the GUI or save it to the .trc file.

The final step in defining the trace specifies the filters, using sp_trace_setfilter (http://msdn.microsoft.com/en-us/library/ms174404.aspx). The first sp_trace_setfilter for 'SQL Server Profiler - f45d52c9-c0eb-45da-8bae-dc6f1a945251 is included in the script by default, and it filters out any 'admin' queries (SELECT SERVERPROPERTY and so on) generated by the Profiler UI.

In this example, we set a filter to send to our target file only those event instances where the number of reads performed by the statement or stored procedure was greater than or equal to 10,000 (@bigintfilter = 10000). Remember, again, that this is late filtering; the event and all its information are captured, and then removed before sending to the client or file, based on the filter criteria.

-- Set the Filters
DECLARE @intfilter INT
DECLARE @bigintfilter BIGINT
EXEC sp_trace_setfilter @TraceID, 10, 0, 7,
    N'SQL Server Profiler - f45d52c9-c0eb-45da-8bae-dc6f1a945251'
SET @bigintfilter = 10000
EXEC sp_trace_setfilter @TraceID, 16, 0, 4, @bigintfilter

Listing 4: Setting the trace filter

The final section of the script starts the trace using sp_trace_setstatus (http://msdn.microsoft.com/en-us/library/ms176034.aspx) and then displays the TraceID, which is unique for each trace and is used to stop the trace later, and also remove the trace definition.

-- Set the trace status to start
EXEC sp_trace_setstatus @TraceID, 1
-- display trace id for future references
SELECT  TraceID = @TraceID
GOTO finish
error: 
SELECT  ErrorCode = @rc
finish: 
go

Listing 5: Starting the trace

If we execute this script, the trace starts and continues to run and collect event data until we stop it.

Now, how do we get from what we know so well, that which is almost second nature, to using Extended Events instead?

Converting Traces to Extended Events Event Sessions

My preferred way to convert an existing trace file definition to an event session is to use a T-SQL stored procedure, written by my colleague Jonathan Kehayias. It is called sp_SQLskills_ConvertTraceToExtendedEvents and you can download the script for the stored procedure from https://www.sqlskills.com/blogs/jonathan/converting-sql-trace-to-extended-events-in-sql-server-2012/. This script only works for SQL Server 2012 and higher, as not all Trace events existed in Extended Events until the 2012 release.

Run the script to create the stored procedure on your instance. Executing the stored procedure only requires four input parameters, as shown in Listing 6.

EXECUTE sp_SQLskills_ConvertTraceToExtendedEvents 
              @TraceID = 2, 
              @SessionName = 'XE_ReadsFilter_Trace', 
              @PrintOutput = 1, 
              @Execute = 0;

Listing 6: Converting a server-side trace to use Extended Events

The @TraceID is the ID of the trace for which you want to create the Extended Events session. Therefore, the trace definition must be exist (the trace does not have to be running, but it can be). In this case, the TraceID is 2 (we output the TraceID in Listing 5).

Use @SessionName to specify the name of the Extended Events session we will create, which in this example is XE_ReadsFilter_Trace. The @PrintOption determines whether the T-SQL for the event session should be displayed in the output window. We want to see this code, thus we use the option of 1. Finally, if you want to create the event session immediately, set @Execute to 1. I want to step through the script first, so we'll specify 0 here.

Executing the stored procedure for our initial ReadsFilter_Trace.trc trace generates the DDL for an Extended Events session, as shown in Listing 7.

IF EXISTS ( SELECT 1
             FROM   sys.server_event_sessions
             WHERE  name = 'XE_ReadsFilter_Trace' )
    DROP EVENT SESSION [XE_ReadsFilter_Trace] ON SERVER;
GO
 CREATE EVENT SESSION [XE_ReadsFilter_Trace] ON SERVER
 ADD EVENT sqlserver.rpc_completed (
    ACTION ( sqlserver.client_app_name   -- ApplicationName from SQLTrace
    , sqlserver.database_id              -- DatabaseID from SQLTrace
    , sqlserver.server_instance_name     -- ServerName from SQLTrace
    , sqlserver.session_id               -- SPID from SQLTrace
                   -- EndTime implemented by another Action in XE already
                   -- StartTime implemented by another Action in XE already
   )
    WHERE 
   ( logical_reads >= 10000 ) ),
 ADD EVENT sqlserver.sql_statement_completed (
    ACTION ( sqlserver.client_app_name   -- ApplicationName from SQLTrace
    , sqlserver.database_id              -- DatabaseID from SQLTrace
    , sqlserver.server_instance_name     -- ServerName from SQLTrace
    , sqlserver.session_id               -- SPID from SQLTrace
                   -- EndTime implemented by another Action in XE already
                   -- StartTime implemented by another Action in XE already
   )
    WHERE 
   ( logical_reads >= 10000 ) )
 ADD TARGET package0.event_file (  SET filename =                                  'C:\temp\XE_ReadsFilter_Trace.xel' ,
                                   max_file_size = 5 ,
                                   max_rollover_files = 1 )
GO

Listing 7: The Extended Events event session

Examining an Extended Events Event Session

As we did with the server-side trace script, generated by Profiler, we will step through the different sections to see how the Extended Events session is created.

Creating the event session

The script includes an IF statement at the beginning to check for an existing event session with the same name, and drop it if it exists. It is included to prevent errors when creating the event session.

IF EXISTS ( SELECT  1
            FROM    sys.server_event_sessions
            WHERE   name = 'XE_ReadsFilter_Trace' )
    DROP EVENT SESSION [XE_ReadsFilter_Trace] ON SERVER;
GO

Listing 8: Checking for the existence of an event session with the same name

The script then creates the event session for the instance using the CREATE EVENT SESSION syntax (http://msdn.microsoft.com/en-us/library/bb677289.aspx). This is analogous to sp_trace_create in the first part of the trace script, but it doesn't have all the same parameters.

/* Extended Events */CREATE EVENT SESSION [XE_ReadsFilter_Trace]
ON SERVER

-- Create a Queue
DECLARE @rc INT
DECLARE @TraceID INT
DECLARE @maxfilesize BIGINT
SET @maxfilesize = 5 
-- Please replace the text
--InsertFileNameHere…etc…
EXEC @rc = sp_trace_create @TraceID OUTPUT, 0, N'InsertFileNameHere',
    @maxfilesize, NULL 
IF ( @rc != 0 )
    GOTO ERROR

Listing 9: Create the event session

Adding an Event and Actions

Next, the script Extended Events script uses the ADD EVENT clause of the CREATE EVENT SESSION DDL to specify the first event, in this case the rpc.completed event, and then specified some additional actions to be performed when the event fires, in this case to collect four additional event data columns.

Listing 10: Adding actions

/*Extended Events*/ADD EVENT sqlserver.rpc_completed (
    ACTION (
      sqlserver.client_app_name
    , sqlserver.database_id
    , sqlserver.server_instance_name
    , sqlserver.session_id
           )

/* Trace */-- Set the events
declare @on bit
set @on = 1
exec sp_trace_setevent @TraceID, 10, 10, @on
exec sp_trace_setevent @TraceID, 10, 3,  @on
exec sp_trace_setevent @TraceID, 10, 12, @on
exec sp_trace_setevent @TraceID, 10, 13, @on
exec sp_trace_setevent @TraceID, 10, 14, @on
exec sp_trace_setevent @TraceID, 10, 15, @on
exec sp_trace_setevent @TraceID, 10, 16, @on
exec sp_trace_setevent @TraceID, 10, 18, @on
exec sp_trace_setevent @TraceID, 10, 26, @on

There are several key differences between Extended Events and Trace to point out here. First, noted that the event name, and the data columns added as actions (e.g. client_app_name, database_id), are in text. No more checking Books Online to find what integer values refer to which event or data column! It makes reading and writing the T-SQL for Extended Events very easy, compared to SQL Trace.

Secondly, note that the extended Events script doesn't appear to specify all of the same data columns. In fact, many of these data columns are collected as part of the default payload for the event. We obviously don't need to specify these default columns in the script, but we can use the UI to find out which data columns comprise the default payload for an event, as we'll see in the next level.

This is an important behavioral difference between Trace and Extended Events. The default behavior of SQL Trace is to collect all potentially useful columns, and then let the user filter out any that are not required. Extended Events is much more efficient. Each event has a default payload consisting of a minimal set of data columns that it always captures, every time the event fires. If we want to capture any data columns that are not part of the default payload, then we need to add them as actions. For the RPC:Completed event, you see the actions client_app_name, database_id, server_instance_name, and session_id, none of which are part of the event's default payload. Collecting these actions is entirely optional.

Since only the default payload event columns are included with the event, the initial data collection overhead is relatively low. Actions are collected after predicate evaluation has occurred, so are only collected for qualifying event instances. These action are collected on the same thread which fires the event, so collecting a high number of actions, or expensive actions (such as a memory dump) can add significant overhead to the Extended Events session. Therefore, it's important to carefully consider what additional data is truly necessary when capturing events. We'll return to this topic in a lot more detail in a later level.

Defining the predicate

After selecting the event and optionally adding actions, the next section of the script defines the filter.

/* Extended Events */    WHERE 
     ( logical_reads >= 10000 )

/* Trace */-- Set the Filters
declare @intfilter int
declare @bigintfilter bigint
exec sp_trace_setfilter @TraceID, 10, 0, 7, N'SQL Server Profiler - f45d52c9-c0eb-45da-8bae-dc6f1a945251'
set @bigintfilter = 10000
exec sp_trace_setfilter @TraceID, 16, 0, 4, @bigintfilter

Listing 11: Adding a filter

Recall that when using Profiler to define a server-side trace, we used sp_trace_setfilter to set a filter that would exclude from our captured event data any SQL executions that did less than 10000 reads. In our event session definition, this filter, also termed a predicate, is in the form of a simple WHERE clause.

Extended Events performs early filtering. In other words, it evaluates this predicate immediately after collecting the base payload for the event, and the event only fires fully for event instances that meet the filter criteria. This causes much lower data collection overhead than the late filtering mechanism of SQL Trace and Profiler.

Adding further events

At this point, the rpc_completed event configuration is complete. To add another event we just use ADD EVENT clause, as shown for the sql:statement_completed event in Listing 12.

ADD EVENT sqlserver.sql_statement_completed(
   ACTION 
   (
           sqlserver.client_app_name         -- ApplicationName from SQLTrace
          , sqlserver.database_id            -- DatabaseID from SQLTrace
          , sqlserver.server_instance_name   -- ServerName from SQLTrace
          , sqlserver.session_id             -- SPID from SQLTrace
                   -- EndTime implemented by another Action in XE already
                   -- StartTime implemented by another Action in XE already
   )
   WHERE 
   (
           logical_reads >= 10000
   )

Listing 12: Adding a second event to the event session

Again, we have the option to select additional, different data columns, as actions. The added flexibility of Extended Events also means that have the option to set a different filter for each event, or the same for all of them. We did not have this option in trace! When you set a filter, it applied to every event. In addition, we can create much more powerful filters in Extended Events, when we start using AND and OR conditions, but let's not get ahead of ourselves!

Specifying a target

After adding all the events, we use ADD TARGET to specify the output destination, or target, to which SQL Server will write the collected event data and associated actions. With trace we only had the option of writing the output to a file, or viewing it live in Profiler, which is not recommended. With Extended Events we have multiple options for targets, including both basic in-memory storage (ring_buffer) and files system storage (event_file), as well as some advanced targets that can perform pre-aggregation of the collected data.

For this example, we're just going to focus on the event_file target, which is analogous to the output file (.trc extension) we're used to from trace, but which was specified as part of the initial trace creation.

/* Extended Events */ADD TARGET package0.event_file
(
      SET filename = 'C:\temp\XE_ReadsFilter_Trace.xel',
             max_file_size = 5,
             max_rollover_files = 1
)

/* Trace */-- Create a Queue
declare @rc int
declare @TraceID int
declare @maxfilesize bigint
set @maxfilesize = 5 
exec @rc = sp_trace_create @TraceID output, 0, N'InsertFileNameHere', @maxfilesize, NULL 
if (@rc != 0) goto error

Listing 13: Specifying the target for the event session

Just as with trace, we specify the maximum size of the file, and we can also set the number of rollover files to create.

Setting event session options

Finally, in our event session definition, we have the option to configure various event session options, such as maximum memory usage and dispatch latency. Since we did not specify any of these in our script, the defaults are used. Session settings will be covered in more detail in a future level.

If you take a minute and read back through the Extended Events session definition in its entirety, you will hopefully find that it's pretty straight-forward and not too tricky once you understand each part of the DDL.

If we execute this script, the event session definition will be created, but the event session will not be running.

Running an event session

As with Trace, Extended Events sessions are not started by default. To start a session, you use the following ALTER statement shown in Listing 14.

ALTER EVENT SESSION [XE_ReadsFilter_Trace]
 ON SERVER
 STATE=START;
GO

Listing 14: Starting the event session

After starting execution, we can run a script to verify that the Extended Events session has started, as well as the Trace.

/* Extended Events */SELECT
  [es].[name] AS [EventSession],
 [xe].[create_time] AS [SessionCreateTime],
  [xe].[total_buffer_size] AS [TotalBufferSize],
  [xe].[dropped_event_count] AS [DroppedEventCount]
FROM [sys].[server_event_sessions] [es]
LEFT OUTER JOIN [sys].[dm_xe_sessions] [xe] ON [es].[name] = [xe].[name];
GO

/* Trace */SELECT 
  [id] AS [TraceID],
  CASE
    WHEN [status] = 0 THEN 'Not running'
    WHEN [status] = 1 THEN 'Running'
  END AS [TraceStatus],
  [start_time] AS [TraceStartTime],
  [buffer_size] AS [BufferSize],
  [dropped_event_count] AS [DroppedEventCount]
FROM [sys].[traces];
GO

Listing 15: Check to see which event sessions and traces are running

In this case, we should see the output shown in Figure 3, which shows the user event session and trace we created, as well as the event sessions and trace that are running by default.

Just as trace has a default trace which is always running (TraceID of 1), Extended Events has the system_health event session. It is not exactly the same as the default trace, and we will examine the system_health session in detail in a future level. If you're using Availability Groups (AG), then the AlwaysOn_health session will also be enabled in order to capture AG-related informational and troubleshooting events.

Figure 3: Which traces and event sessions are running?

After we've run our trace and event session, we stop the event session using ALTER SESSION, and the trace using sp_trace_setstatus.

/* Extended Events */ALTER EVENT SESSION [XE_ReadsFilter_Trace]
  ON SERVER
  STATE=STOP;
GO

/* Trace */DECLARE @TraceID INT = 2;
EXEC sp_trace_setstatus @TraceID, 0; 
GO

Listing 16: Stopping the extended events session and trace

At this point, no data is being collected, but the definitions for both the trace and event session still exist. We can re-start either if needed, or we can remove the definitions entirely.

/* Extended Events */DROP EVENT SESSION [XE_ReadsFilter_Trace]
ON SERVER;
GO

/* Trace */DECLARE @TraceID INT = 2;
EXEC sp_trace_setstatus @TraceID, 2; 
GO

Listing 17: Removing the extended events session and trace definitions

It is not required that you drop the extended events session when finished. This may be a habit for many of you who have used SQL Trace, as any time the instance restarts, all existing trace definitions are lost, with the exception of the default trace. This another important difference between Extended Events and SQL Trace: event session definitions for Extended Events are saved in the server metadata and so they persist between restarts. Having created an event session that works for you, you can just start and stop it as needed.

Summary

You now have a method to map what you know about Profiler and SQL Trace to Extended Events, and we used T-SQL to get there. Our next step is to take what we've seen with the DDL, and transfer that knowledge to the Extended Events UI. We'll tackle that in our next level!