Extended Events are a lightweight monitoring system offered by SQL Server. Because the architecture uses so few system resources, they scale very well and allow you to monitor their instances, with minimal impact on user activity. They are also highly configurable, which gives you in your role as a DBA a wide range of options for capturing details from a very fine grain, such as page splits, to higher-level detail, such as CPU utilization. You can also correlate Extended Events with operating system data to provide a holistic picture when troubleshooting issues. The predecessor to Extended Events was SQL Trace, and its GUI, called Profiler. This is now deprecated for use with the Database Engine, and it is recommended that you only use it for tracing Analysis Service activity.

Extended Events Concepts

Extended Events have a rich architecture, which consists of events, targets, actions, types, predicates, and maps. These artifacts are stored within a package, which is, in turn, stored within a module, which can be either a .dll or an executable. We discuss these concepts in the following sections.

Packages

A package is a container for the objects used within Extended Events. Here are the four types of SQL Server package:

• Package0: The default package, used for Extended Events system objects.
• Sqlserver: Used for SQL Server–related objects.
• Sqlos: Used for SQLOS-related objects.
• SecAudit: Used by SQL Audit; however, its objects are not exposed.

Events

An event is an occurrence of interest that you can trace. It may be a SQL batch completing, a cache miss, or a page split, or virtually anything else that can happen within the Database Engine, depending on the nature of the trace that you are configuring. Each event is categorized by channel and keyword (also known as category). A channel is a high-level categorization, and all events in SQL Server 2014 fall into one of the channels described in Table 19-1.

Keywords, or categories, are much more fine grain. There are 47 categories within SQL Server 2014, which are detailed in Table 19-2.

Targets

A target is the consumer of the events; essentially, it is the device to which the trace data will be written. The targets available within SQL Server 2014 are detailed in Table 19-3.

Actions

Actions are commands that allow additional information to be captured when an event fires. An action is fired synchronously when an event occurs and the event is unaware of the action. There are 50 actions available in SQL Server 2014 that allow you to capture a rich array of information, including the statement that caused the event to fire, the login that ran this statement, the transaction ID, the CPU ID, and the call stack.

Predicates

Predicates are filter conditions that you can apply before the system sends events to the target. It is possible to create simple predicates, such as filtering statements completing based on a database ID, but you can also create more complex predicates, such as only capturing a long IO that has a duration greater than five seconds, or only capturing the role change of an AlwaysOn Availability Group replica if it happens more than twice.

Predicates also fully support short-circuiting. This means that if you use multiple conditions within a predicate, then the order of predicates is important, because if the evaluation of the first predicate fails, the second predicate will not be evaluated. Because predicates are evaluated synchronously, this can have an impact on performance. Therefore, it is prudent to design you predicates in such a way that predicates that are least likely to evaluate to true come before predicates that are very likely to evaluate to true. For example, imagine that you are planning to filter on a specific database (with a database ID of 6) that is the target of a high percentage of the activity on the instance, but you also plan to filter on a specific user ID (MyUser), which is responsible for a lower percentage of the activity. In this scenario, you would use the WHERE (([sqlserver].[username]=N’MyUser’) AND ([sqlserver].[database_id]=(6))) predicate to first filter out activity that does not relate to MyUser and then filter out activity that does not relate to database ID 6.

Types and Maps

All objects within a package are assigned a type. This type is used to interpret the data stored within the byte collection of an object. Objects are assigned one of the following types:

• Action
• Event
• Pred_compare (retrieve data from events)
• Pred_source (compare data types)
• Target
• Type

You can find a list of predicate comparators and predicate sources by executing the queries in Listing 19-1.

--Retrieve list of predicate comparators

SELECT name
        ,description,
    (SELECT name
                FROM sys.dm_xe_packages
                WHERE guid = xo.package_guid) Package
FROM sys.dm_xe_objects xo
WHERE object_type = ’pred_compare’
ORDER BY name ;

--Retrieve list of predicate sources

SELECT name
        ,description,
    (SELECT name
                FROM sys.dm_xe_packages
                WHERE guid = xo.package_guid) Package
FROM sys.dm_xe_objects xo
WHERE object_type = ’pred_source’
ORDER BY name ;

A map is a dictionary that maps internal ID vales to strings that DBAs can understand. Map keys are only unique within their context and are repeated between contexts. For example, within the statement_recompile_cause context, a map_key of 1 relates to a map_value of Schema Changed. Within the context of a database_sql_statement type, however, a map_key of 1 relates to a map_value of CREATE DATABASE. You can find a complete list of mappings by using the sys.dm_xe_map_values DMV, as demonstrated in Listing 19-2. To check the mappings for a specific context, filter on the name column.

SELECT
        map_key
        , map_value
        , name
FROM sys.dm_xe_map_values ;

Sessions

A session is essentially a trace. It can contain events from multiple packages, actions, targets, and predicates. When you start or stop a session, you are turning the trace on or off. When a session starts, events are written to memory buffers and have predicates applied before they are sent to the target. Therefore, when creating a session, you need to configure properties, such as how much memory the session can use for buffering, what events can be dropped if the session experiences memory pressure, and the maximum latency before the events are sent to the target.

Creating an Event Session

You can create an event session using either the New Session Wizard, the New Session Dialog Box, or via T-SQL. We explore each of these options in the following sections. Before creating any event sessions, however, we first create the Chapter19 database, populate it with data, and create stored procedures, which we use in later examples. Listing 19-3 contains the script to do this.

--Create the database

CREATE DATABASE Chapter19 ;
GO

USE Chapter19
GO

--Create and populate numbers table

DECLARE @Numbers TABLE
(
        Number        INT
)

;WITH CTE(Number)
AS
(
        SELECT 1 Number
        UNION ALL
        SELECT Number + 1
        FROM CTE
        WHERE Number < 100
)
INSERT INTO @Numbers
SELECT Number FROM CTE;

--Create and populate name pieces

DECLARE @Names TABLE
(
        FirstName        VARCHAR(30),
        LastName        VARCHAR(30)
);

INSERT INTO @Names
VALUES(’Peter’, ’Carter’),
                 (’Michael’, ’Smith’),
                 (’Danielle’, ’Mead’),
                 (’Reuben’, ’Roberts’),
                 (’Iris’, ’Jones’),
                 (’Sylvia’, ’Davies’),
                 (’Finola’, ’Wright’),
                 (’Edward’, ’James’),
                 (’Marie’, ’Andrews’),
                 (’Jennifer’, ’Abraham’);

--Create and populate Customers table

CREATE TABLE dbo.Customers
(
        CustomerID           INT                NOT NULL        IDENTITY        PRIMARY KEY,
        FirstName            VARCHAR(30)        NOT NULL,
        LastName             VARCHAR(30)        NOT NULL,
        BillingAddressID     INT                NOT NULL,
        DeliveryAddressID    INT                NOT NULL,
        CreditLimit          MONEY              NOT NULL,
        Balance              MONEY              NOT NULL
);

SELECT * INTO #Customers
FROM
         (SELECT
                 (SELECT TOP 1 FirstName FROM @Names ORDER BY NEWID()) FirstName,
                 (SELECT TOP 1 LastName FROM @Names ORDER  BY NEWID()) LastName,
                 (SELECT TOP 1 Number FROM @Numbers ORDER  BY NEWID()) BillingAddressID,
                 (SELECT TOP 1 Number FROM @Numbers ORDER  BY NEWID()) DeliveryAddressID,
                 (SELECT TOP 1 CAST(RAND() * Number AS INT) * 10000
                  FROM @Numbers
                  ORDER BY NEWID()) CreditLimit,
                 (SELECT TOP 1 CAST(RAND() * Number AS INT) * 9000
                  FROM @Numbers
                  ORDER BY NEWID()) Balance
        FROM @Numbers a
        CROSS JOIN @Numbers b
) a;

INSERT INTO dbo.Customers
SELECT * FROM #Customers;
GO

CREATE INDEX idx_LastName ON dbo.Customers(LastName)
GO

CREATE PROCEDURE UpdateCustomerWithPageSplits
AS
BEGIN
        UPDATE dbo.Customers
        SET FirstName = cast(FirstName + replicate(FirstName,10) as varchar(30))
        ,LastName = cast(LastName + replicate(LastName,10) as varchar(30)) ;
END ;
GO

CREATE PROCEDURE UpdateCustomersWithoutPageSplits
AS
BEGIN
        UPDATE dbo.Customers
        SET CreditLimit = CreditLimit * 1.5
        WHERE Balance < CreditLimit - 10000 ;
END ;
GO

Using the New Session Wizard

You can invoke the New Session Wizard from SQL Server Management Studio by drilling through Management | Extended Events in Object Explorer and selecting New Session Wizard from the Sessions context menu. After you pass through the Introduction page of the wizard, the Set Session Properties page displays, as illustrated in Figure 19-1. Use this page to specify a name for the session and to indicate if the session should start automatically each time the instance starts. We use the session to track log file IO, so we configure it to start when the instance starts and name it LogFileIO.

On the Choose Template page of the wizard, you can select a predefined template from the drop-down list. Predefined templates are configured with sets of events and predicates that are useful for DBAs in common scenarios. After you select a template, you are able to refine it to suit your individual requirements, or alternatively, you can choose to not use a template. This option configures an empty session into which you can add all events and predicates manually. The Choose Template page is illustrated in Figure 19-2. Because we plan to monitor log file IO, unsurprisingly, we select the Database Log File IO Tracing template.

On the Select Events To Capture page, we can add or remove events we would like captured in the session. You can search the Event Library for a keyword, or filter by channel or category. When you highlight an event, a description displays, along with the Event fields. You can then select the event by using the right pointing arrow, or you can remove an event by selecting it in the right hand pane and using the left pointing arrow. In our case, we have filtered on the IO category and added the database_log_flush_wait event, as shown in Figure 19-3.

On the Capture Global Fields page, we can specify any actions that we need. In our scenario, in addition to the preselected actions, we choose to capture the database_name, as demonstrated in Figure 19-4. Actions are known as global fields, because they are available to all events as opposed to event fields, which are local to their specific event.

On the Set Session Event Filters page, you can review the filters that have been applied to the template in the top pane. In the bottom pane, you can add additional predicates to target the trace to your specific needs. Figure 19-5 illustrates adding a predicate that filters on the database_name global field so that only events from this database are captured.

On the Specify Session Data Storage page of the wizard you can specify the target for the event session. In our scenario, we choose to write the events to a file. Therefore, we need to specify the fully qualified filename for the trace file on the server. The default file extension is .XEL. We also specify the maximum file size for the capture, and if we want new files to be created when this maximum size is reached, we also need to specify a value for the maximum number of files, as shown in Figure 19-6.

The Summary page, displayed in Figure 19-7, summarizes how the Event Session will be configured; it also gives us the option of scripting the configuration, which is highly recommended for documentation, change control, and code reusability.

Finally, the Create Event Session page informs us of the status of the Event Session creation (see Figure 19-8). On this page, we specify whether we want the session to start immediately, and if we select this option, we are also given the opportunity of immediately launching the live data viewer, which we discuss later in this chapter.

Using the New Session Dialog Box

You can access the New Session dialog box from SQL Server Management Studio by first drilling through Management | Extended Events in Object Explorer, and then by selecting New Session from the Sessions context menu. We use the New Session dialog box to create a session that monitors page splits and correlates them with the stored procedures that caused them to occur. To allow this, we need to enable causality tracking, which gives each event an additional GUID value, called an ActivityID, and a sequence number; together, these allow the events to be correlated.

When you invoke the dialog box, the General page displays, as illustrated in Figure 19-9. On this page, you can specify a name for the session, choose whether or not it should start automatically after it is completed and automatically when the instance starts, whether the live data view launches after the session completes, and if causality tracking should be enabled.

Because we are going to monitor page splits, we name the session PageSplits and specify that the session should start automatically, both after creation and also when the instance starts. We also turn on causality tracking.

On the Events page, we first search for and select the page_splits and module_start events, as shown in Figure 19-10. The module_start event is triggered every time a programmable object fires.

We now need to use the Configure button to configure each of the events. In the Global Fields (Actions) tab of the Configure screen, we select the nt_username and database_name actions for the module_start event, as illustrated in Figure 19-11.

On the Filter (Predicates) tab, we configure the page_splits event to be filtered on the database_name, which is Chapter19 in this case, as show in Figure 19-12. This means that only page splits relating to this database are captured. We do not filter the module_start event on the database_name, because the procedure that caused the page splits could, in theory, have been fired from any database.

In the Event Fields tab of the Configure screen, the fields that relate to the event are displayed. If there are any optional fields, then we are able to select them. Figure 19-13 shows that we have selected the statement field for the module_start event.

On the Data Storage page of the New Session dialog box, we configure the target(s). For our scenario, we configure a single event file target, as demonstrated in Figure 19-14. The parameters are context sensitive, depending on the type of target that you select. Because we have selected a file target, we need to configure the location and maximum size of the file. We also need to specify if we want new files to be created if the initial file becomes full, and if so, how many times this should happen.

On the Advanced page, shown in Figure 19-15, we can specify the desired behavior in the event of memory pressure: whether single-event loss is acceptable, whether multiple-event loss is acceptable, or whether there should be no event loss at all. We can also set the minimum and maximum size for events and how memory partitioning should be applied. This is discussed in more detail in the following section. Additionally, we can configure dispatch latency. This indicates the maximum amount of time that an event remains in the buffers before it is flushed to disk.

Using T-SQL

You can also create event sessions via T-SQL using the CREATE EVENT SESSION DDL statement. The command accepts the arguments detailed in Table 19-4.

The statement also accepts the WITH options, detailed in Table 19-5. The WITH statement is specified once, at the end of the CREATE EVENT SESSION statement.

The script in Listing 19-4 demonstrates how you can use T-SQL to create the PageSplits session that we created in the previous section.

CREATE EVENT SESSION PageSplits
ON SERVER
--Add the module_start event
ADD EVENT sqlserver.module_start(SET collect_statement=(1)
--Add actions to the module_start event
    ACTION(sqlserver.database_name,sqlserver.nt_username)),
--Add the page_split event
ADD EVENT sqlserver.page_split(
--Create the predicate to filter on the database name
    WHERE ([sqlserver].[database_name]=N’Chapter19’))
--Add the event_file target
ADD TARGET package0.event_file(SET filename=N’c:MSSQLPageSplits’,max_file_size=(512))
WITH (MAX_MEMORY=4096 KB, EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,
        MAX_DISPATCH_LATENCY=30 SECONDS, MAX_EVENT_SIZE=0 KB, MEMORY_PARTITION_MODE=NONE,
        TRACK_CAUSALITY=ON, STARTUP_STATE=ON);
GO

--Start the session
ALTER EVENT SESSION PageSplits
ON SERVER
STATE = start;

Viewing the Collected Data

SQL Server provides a data viewer that you can use for basic analysis of event data from a file or live data from the buffers. For more complex analysis, however, you can access and manipulate the event data via T-SQL. The following sections discuss each of these methods of analysis.

Analyzing Data with Data Viewer

You can use the data viewer to watch live data as it hits the buffers by drilling through Management | Extended Events | Sessions in Object Explorer and selecting Watch Live Data from the Session context menu. Alternatively, you can use it to view data in the target by drilling through the session and selecting View Target Data from the Target context menu.

The script in Listing 19-5 inserts data into the Customers table in the Chapter19 database, which causes IO activity for the transaction log, which is captured by our LogFileIO session.

USE Chapter19
GO

--Create and populate numbers table

DECLARE @Numbers TABLE
(
        Number        INT
)

;WITH CTE(Number)
AS
(
        SELECT 1 Number
        UNION ALL
        SELECT Number + 1
        FROM CTE
        WHERE Number < 100
)
INSERT INTO @Numbers
SELECT Number FROM CTE;

--Create and populate name pieces

DECLARE @Names TABLE
(
        FirstName        VARCHAR(30),
        LastName        VARCHAR(30)
);

INSERT INTO @Names
VALUES(’Peter’, ’Carter’),
                 (’Michael’, ’Smith’),
                 (’Danielle’, ’Mead’),
                 (’Reuben’, ’Roberts’),
                 (’Iris’, ’Jones’),
                 v(’Sylvia’, ’Davies’),
                 (’Finola’, ’Wright’),
                 (’Edward’, ’James’),
                 (’Marie’, ’Andrews’),
                 (’Jennifer’, ’Abraham’);

--Insert to Customers

SELECT * INTO #Customers
FROM
         (SELECT
                 (SELECT TOP 1 FirstName FROM @Names ORDER BY NEWID()) FirstName,
                 (SELECT TOP 1 LastName FROM @Names ORDER BY NEWID()) LastName,
                 (SELECT TOP 1 Number FROM @Numbers ORDER BY NEWID()) BillingAddressID,
                 (SELECT TOP 1 Number FROM @Numbers ORDER BY NEWID()) DeliveryAddressID,
                 (SELECT TOP 1 CAST(RAND() * Number AS INT) * 10000
                  FROM @Numbers
                  ORDER BY NEWID()) CreditLimit,
                 (SELECT TOP 1 CAST(RAND() * Number AS INT) * 9000
                  FROM @Numbers
                  ORDER BY NEWID()) Balance
        FROM @Numbers a
        CROSS JOIN @Numbers b
        CROSS JOIN @Numbers c
) a;

INSERT INTO dbo.Customers
SELECT * FROM #Customers;
GO

If we now open the data viewer for the event_file target under the LogFileIO session in Object Explorer, we see the results illustrated in Figure 19-16. The viewer shows each event and timestamp in a grid; selecting an event exposes the Details pane for that event.

Notice that a data viewer toolbar is displayed in SQL Server Management Studio, as illustrated in Figure 19-17. You can use this toolbar to add or remove columns from the grid, as well as to perform grouping and aggregation operations.

Clicking the Choose Columns button invokes the Choose Columns dialog box. We use this dialog box to add the duration and wait_type columns to the grid, as shown in Figure 19-18.

We can now use the Grouping button to invoke the Grouping dialog box. In this dialog box, we choose to group the events by their name, as shown in Figure 19-19.

We can now use the Aggregation button to invoke the Aggregation dialog box. We can use this dialog box to apply aggregate functions, such as SUN, AVG, or COUNT to the data. It is also possible to sort the data by an aggregated value. Figure 19-20 shows that we are using this dialog box to add a SUM of the wait durations.

In the data viewer grid, we are now able to see a SUM of the duration column for the wait_info events, and if we expand this group, it displays the granular details, as shown in Figure 19-21. We see N/A as the subtotal for groups, which do not have a duration field.

Analyzing Data with T-SQL

If you require more complex analysis of the data, then you can achieve this via T-SQL. The sys.fn_xe_file_target_read_file function makes this possible by reading the target file and returning one row per event in XML format. The sys.fn_xe_file_target_read_file accepts the parameters detailed in Table 19-6.

The sys.fn_xe_file_target_read_file procedure returns the columns detailed in Table 19-7.

Because the event data is returned in XML format, we need to use XQuery to shred the nodes into relational data. A full description of XQuery is beyond the scope of this book, but Microsoft provides an XQuery language reference on msdn.microsoft.com.

The script in Listing 19-6 runs the UpdateCustomersWithPageSplits and UpdateCustomersWithoutPageSplits procedures in the Chapter19 database before extracting the event data using the sys.fn_xe_file_target_read_file. We then use the XQuery Value method to extract relational values from the XML results. Finally, because we have turned on causality tracking, we group the data by the correlation GUID to see how many page splits each stored procedure caused. UpdateWithoutPageSplits provides a contrast.

--Run the update procedures

EXEC UpdateCustomersWithoutPageSplits ;
GO

EXEC UpdateCustomerWithPageSplits ;
GO

--Wait 30 seconds to allow for the XE buffers to be flushed to the target

WAITFOR DELAY ’00:00:30’ ;

--Query the XE Target

SELECT c.procedurename, d.pagesplits
 FROM
 (
        SELECT
                correlationid,
                COUNT(*) -1 PageSplits -- -1 to remove the count of the module_start event
        FROM
        (
               SELECT CapturedEvent,
                        xml:data.value(’(/event/data[@name=’’object_name’’]/value)[1]’, ’nvarchar(max)’) procedurename,  --extract procedure name
                        xml:data.value(’(/event/action[@name=’’attach_activity_id’’]/value)[1]’, ’uniqueidentifier’) correlationid --extract Correlation ID
                FROM
                 (
--Query the fn_xe_file_target_read_file function, to extract the raw XML
                                SELECT
                                OBJECT_NAME CapturedEvent,
                                CAST(event_data AS XML) xml:data
                                FROM
sys.fn_xe_file_target_read_file(’C:mssqlpagesplits*.xel’, NULL , NULL, NULL) as XE ) a
                ) b
                GROUP BY correlationid
        ) d
INNER JOIN --Self join, to allow the count of page splits
(
        SELECT CapturedEvent,
                xml:data.value(’(/event/data[@name=’’object_name’’]/value)[1]’, ’nvarchar(max)’) procedurename,
                xml:data.value(’(/event/action[@name=’’attach_activity_id’’]/value)[1]’, ’uniqueidentifier’) correlationid
        FROM
         (
                SELECT object_name CapturedEvent,
                CAST(event_data AS XML) xml:data
                FROM
                sys.fn_xe_file_target_read_file(’C:mssqlpagesplits*.xel’, NULL , NULL, NULL) as XE ) a
        ) c
ON c.correlationid = d.correlationid
        AND c.procedurename IS NOT NULL ;

Correlating Extended Events with Operating System Data

Extended Events offer the capability to integrate with operating system–level data. The following sections discuss how to correlate SQL Server events with Perfmon data and other operating system–level events.

Correlating Events with Perfmon Data

Before Extended Events were introduced, DBAs used a tool called SQL Trace and its GUI, Profiler, to capture traces from SQL Server; it was possible to correlate this data with data from Perfmon. With Extended Events, you do not often need to make this correlation, because Extended Events include Perfmon counters for processor, logical disk, and system performance objects, such as context switches and file writes. Therefore, you can correlate SQL Server events with operating system counters by adding these objects to the session and by following the T-SQL analysis techniques discussed in the previous section.

The script in Listing 19-7 demonstrates creating an event session that captures statements executed within the instance, alongside processor counters. Processor counters are captured every 15 seconds for each processor in the system. The results are saved to an event file target and an ETW target.

CREATE EVENT SESSION Statements_with_Perf_Counters
ON SERVER
--Add the Events and Actions relating to each Event
ADD EVENT sqlserver.error_reported(
    ACTION(sqlserver.client_app_name,sqlserver.database_id,sqlserver.query_hash,sqlserver.session_id)
    WHERE ([package0].[greater_than_uint64]([sqlserver].[database_id],(4)) AND
[package0].[equal_boolean]([sqlserver].[is_system],(0)))),
ADD EVENT sqlserver.module_end(SET collect_statement=(1)
    ACTION(sqlserver.client_app_name,sqlserver.database_id,sqlserver.query_hash,sqlserver.session_id)
    WHERE ([package0].[greater_than_uint64]([sqlserver].[database_id],(4)) AND
[package0].[equal_boolean]([sqlserver].[is_system],(0)))),
ADD EVENT sqlserver.perfobject_processor,
ADD EVENT sqlserver.rpc_completed(
    ACTION(sqlserver.client_app_name,sqlserver.database_id,sqlserver.query_hash,sqlserver.session_id)
    WHERE ([package0].[greater_than_uint64]([sqlserver].[database_id],(4)) AND
[package0].[equal_boolean]([sqlserver].[is_system],(0)))),
ADD EVENT sqlserver.sp_statement_completed(SET collect_object_name=(1)
    ACTION(sqlserver.client_app_name,sqlserver.database_id,sqlserver.query_hash,sqlserver.query_plan_hash,sqlserver.session_id)
    WHERE ([package0].[greater_than_uint64]([sqlserver].[database_id],(4)) AND [package0].[equal_boolean]([sqlserver].[is_system],(0)))),
ADD EVENT sqlserver.sql_batch_completed(
    ACTION(sqlserver.client_app_name,sqlserver.database_id,sqlserver.query_hash,sqlserver.session_id)
    WHERE ([package0].[greater_than_uint64]([sqlserver].[database_id],(4)) AND
[package0].[equal_boolean]([sqlserver].[is_system],(0)))),
ADD EVENT sqlserver.sql_statement_completed(
    ACTION(sqlserver.client_app_name,sqlserver.database_id,sqlserver.query_hash,sqlserver.query_plan_hash,sqlserver.session_id)
    WHERE ([package0].[greater_than_uint64]([sqlserver].[database_id],(4)) AND
[package0].[equal_boolean]([sqlserver].[is_system],(0))))
--Add the Targets
ADD TARGET package0.event_file(SET filename=N’C:MSSQLStatementsAndProcessorUtilization.xel’),
ADD TARGET package0.etw_classic_sync_target(SET default_etw_session_logfile_path=N’C:MSSQLStatementsWithPerfCounters.etl’)
WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=30 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=ON,STARTUP_STATE=OFF) ;
GO

--Start the instance
ALTER EVENT SESSION Statements_with_Perf_Counters
ON SERVER
STATE = start;

Integrating Event Sessions with Operating System–Level Events

There are instances in which you may need to integrate event session data with operating system data other than Perfmon counters that SQL Server provides. For example, imagine a scenario in which you have an application that exports SQL Server data to flat files in the operating system so that a middleware product, such as BizTalk, can pick them up. You are having trouble generating some files and you need to view the process flow—from SQL statement being run through to the WMI events being triggered in the operating system. For this, you need to merge event session data with a trace of WMI events. You can achieve this through the ETW (Event Tracking for Windows) architecture.

To demonstrate this, we first create an event trace session in Performance Monitor using the WMI provider, and then we integrate it with the event session that we created in the previous section. (You can find Performance Monitor in Administrative Tools in Windows.) After we open Performance Monitor, we select New | Data Collector Set from the Event Trace Sessions context menu, which causes the Create New Data Collector Set wizard to be invoked. On the first page of the wizard, specify a name for the Collector Set, as illustrated in Figure 19-22, and specify if the Data Collector set should be configured manually or based on a template. In our scenario, we choose to configure it manually.

On the page for enabling event trace providers, we can use the Add button to add the WMI-Activity provider, as illustrated in Figure 19-23.

We now use the Edit button to invoke the Properties dialog box. Here, we add the Trace and Operational categories by using the check boxes, as shown in Figure 19-24.

After exiting the Properties dialog box, we move to the next page of the wizard, where we can configure the location where the trace file is stored. We configure the trace file to be saved to the same location as our event session trace, as shown in Figure 19-25.

On the final page of the wizard, illustrated in Figure 19-26, we leave the default options of running the trace under the default account and then save and close the trace.

In Performance Monitor, our trace is now visible in the Event Trace Sessions folder, but showing as stopped. We can use the context menu of the trace to start the Data Collector Set, as shown in Figure 19-27. Also notice that there is a Data Collector Set called XE_DEFAULT_ETW_SESSION. Our extended event session created this because we created an ETW target. This session is required for integrating the data.

Now that both the WMISession and Statements_with_Perf_Counters sessions are started, we use the BCP command in Listing 19-8 to generate activity, which causes events to fire in both sessions.

bcp chapter19.dbo.customers out c:mssqldump.dat -S .PROSQLADMIN -T -c

We now need to ensure that the buffers of both sessions are flushed to disk. We do this by stopping both sessions. After stopping the Statements_with_Perf_Counters session, we also need to stop the XE_DEFAULT_ETW_SESSION ETW session in Performance Monitor. You can stop the Statements_with_Perf_Counters session by using the T-SQL command in Listing 19-9.

ALTER EVENT SESSION Statements_with_Perf_Counters
ON SERVER
STATE = stop;

You can stop the WMISession and XE_DEFAULT_ETW_SESSION by selecting Stop from their respective context menus in Performance Monitor.

The next step is to merge the two trace files together. You can achieve this from the command line by using the XPERF utility with the -Merge switch (demonstrated in Listing 19-10). This command merges the files together, with StatementsWithPerfCounters.etl being the target file. You should navigate to the C:Program Files (x86)Windows Kits8.1Windows Performance Toolkit folder, before running the script.

xperf -merge c:mssqlwmisession.etl C:MSSQLStatementsWithPerfCounters.etl

Now that all events are in the same file, you can open and analyze this .etl file with Windows Performance Analyzer, which is available as part of the Windows Performance Toolkit, as shown in Figure 19-28. Once installed, you can access Windows Performance Analyzer via the Windows Start menu. A full discussion of Windows Performance Analyzer is beyond the scope of this book, but you can find it in Administrative Tools in Windows after it’s installed. You will find full documentation on msdn.microsoft.com.

Summary

Extended Events introduce new concepts that you must understand in order to fully harness their power. Events are points of interest that are captured in a trace, whereas actions provide extended information, in addition to the event columns. Predicates allow you to filter events in order to provide a more targeted trace, and targets define how the data is stored. A session is the trace object itself, and it can be configured to include multiple events, actions, predicates, and targets.

You can create an event session through the New Session Wizard, an easy and quick method that exposes Templates; via the New Session dialog box; or of course, via T-SQL. When creating a session via T-SQL, you use the CREATE EVENT SESSION DDL statement to configure all aspects of the trace.

Each Extended Event artifact is contained within one of four packages: Package0, Sqlserver, Sqlos, and SecAudit. The contents of SecAudit are not exposed, however, since these are used internally to support SQL Audit functionality, which is discussed in Chapter 9.

You can view data using the data viewer. The data viewer allows you to watch live data in the session’s buffers, and it also supports viewing target data from the Event File, Event Count, and Histogram Target types. The data viewer provides basic data analysis capability, including grouping and aggregating data.

For more complex data analysis, you can open targets in T-SQL. To open an Event File target, use the sys. fn_xe_file_target_read_file results system stored procedure. You then have the power of T-SQL at your disposal for complex analysis requirements.

You can correlate events by turning on causality tracking within the session. This adds a GUID and a sequence number to each event so that you can identify relationships. You can also easily correlate SQL events with Perfmon data, because Extended Events expose processor, logical disk, and system performance counters. To correlate events with other operating system–level events, event sessions can use the ETW target, which you can then merge with other data collector sets to map Extended Events to events from other providers in the ETW architecture.