Packages

Events

Targets

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 67 actions available in SQL Server 2022, which 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 I/O 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

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.

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 20-1. 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 20-2. 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 20-3.

On the Filter (Predicate) tab, we configure the page_splits event to be filtered on the database_name, which is Chapter20 in this case, as show in Figure 20-4. 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 20-5 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 20-6. 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, 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

The script in Listing 20-5 demonstrates how you can use T-SQL to create a session called LogFileIO. This session is similar to the Database Log File IO Tracking template, provided through the GUI. The difference is that we are additionally capturing the sqlos.wait_completed event and the sqlserver.DatabaseName global field, and filtering on this, so that only details of the Chapter20 transaction log are tracked.

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.

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 20-7. 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 20-8. 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 20-9.

We can now right-click the wait_type column, and select the Group By This Column option. This will cause all of the events to be rolled up, to the level of Wait Type.

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 SUM, AVG, or COUNT, to the data. It is also possible to sort the data by an aggregated value. Figure 20-10 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_type, and if we expand this group, it displays the granular details, as shown in Figure 20-11.

Analyzing Data with T-SQL

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 20-7 runs the UpdateCustomersWithPageSplits and UpdateCustomersWithoutPageSplits procedures in the Chapter20 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.

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.

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 20-12, 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 20-13.

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 20-14.

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 20-15.

On the final page of the wizard, 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 20-16. 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.

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

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 20-17. 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.