SQL Server Extended Events Step 2: Create a basic event session using the UI

In the first step we describe how to customize a trace in the profiler and let it run on the server side to create a trace file. We then converted the trace definition to the script that created the extended Events session through the sp_sqlskills_converttracetoextendedevents stored procedure of Jonathan Kehayias. Hopefully it's a bridge for you to start with SQL Trace leading to extended events. Of course, it also provides an effective way to convert existing SQL Trace libraries to extend events.

However, what happens when we need to create a new extended events session, starting from scratch? Yes, we can use T-SQL to define sessions, but in SQL Server2012 and later, UI tools are integrated in SSMs, and we can use it to define event sessions, start, stop them, and directly use and analyze the data directly on the SMSs UI.

In this step, we will introduce the basic use of the New Session dialog box, use it to create a new event session, define its events, actions, and predicates, and establish the target of an event session to collect the data for the event.

Start with the UI

There is no UI integrated for extended events in SQL Server 2008 and in SQL Server 2008r2. If you are using these versions, you can choose to define event sessions using T-SQL, or install the SSMs plugin (http://extendedeventmanager.codeplex.com/) for Jonathan Kehayias. If you're using Management Studio, you can connect to an earlier version of SQL Server, but you can't find the extended Events UI for instances of version 2008 and 2008r2. Jonathan provides a standalone plugin for SSMS 2012, which provides a simple UI with connection 2008 and 2008r2.

Because of the lack of UI in SQL Server 2008 and 2008r2, this also means that we must use T-SQL and XQuery to handle event data. Fortunately, however, in the SQL Server 2012 UI We can read the target files generated by 2008 and 2008r2, so it is worth installing SSMS 2012 and analyzing the event data using its UI.

For the remainder of this order to be understandable, I assume that you are using SQL Server 2012 and the above version of the UI. To access extended Events in SSMs, expand the SQL instance, and in the Object Browser, switch to management | Extended events, and expand sessions, we can see an event session that already exists in the instance.

If you have completed the first order, you should be able to see two built-in extended events sessions Alwayson_health and System_health, plus the xe_readsfilter_trace session we created.

Figure 1:viewing Event sessions in the SSMS UI

As we mentioned in the first order, one of the benefits of Extended events is that the session definition is saved in the server metadata, even if the instance is restarted and persisted. This is not the definition of trace, we have to implement a custom stored procedure to create the trace again after the instance restarts.

We can also have a lot of event sessions, but we don't activate them. This is possible in trace, but we do not usually use this, possibly because the output target file name is hardcoded in the trace definition. When we want to stop or restart the trace, we need to make sure that the output file is deleted before it is started, or that the trace is set for rolling file updates. Extended events addresses this issue, which we describe in this first-order data storage page: definition goals.

Xe_readsfilter_trace session on the right-click menu, select Script Session as | CREATE to | New Query Editor window, the event session definition will be output to a new queries window, as shown in Listing1. This code is the same as the functionality generated by using Sp_sqlskills_converttracetoextendedevents in the first order. You will notice, however, that there are some small differences here. First, the SQL Server stored script definition no longer contains code comments for mapping SQL Trace columns.
Second, SQL Server contains some additional options that we have not seen in the WITH clause at the end of the script (described in the next step). All session options are default values, which is why they are not generated in the first order of trace conversions.

CREATEEVENT SESSION[Xe_readsfilter_trace]  onSERVERADDEVENT sqlserver.rpc_completed (ACTION (Sqlserver.client_app_name, sqlserver.database_id, Sqlserver.serve R_instance_name, sqlserver.session_id)WHERE(Logical_reads>= 10000 ) ),ADDEVENT sqlserver.sql_statement_completed (ACTION (Sqlserver.client_app_name, sqlserver.database_id, Sqlse Rver.server_instance_name, sqlserver.session_id)WHERE(Logical_reads>= 10000 ) )ADDTARGET Package0.event_file (SETFileName= 'C:\temp\XE_ReadsFilter_Trace.xel', Max_file_size=(5), Max_rollover_files=(1 ) ) with(max_memory= 4096KB, Event_retention_mode=Allow_single_event_loss, Max_dispatch_latency=  -SECONDS, Max_event_size= 0KB, Memory_partition_mode=NONE, Track_causality= OFF, Startup_state= OFF )GO

Listing 1:scripting out the existing Xe_readsfilter_trace event session

We then use the UI to create an event session with the same event, Actions, and predicates as Xe_readsfilter_trace, but with a different name. At the end of this process, I'll tell you how to convert a feature definition that we've done on the UI to a SQL Server-generated T-SQL script.

Create a new event session on the UI

We can use the New Session Wizard or the new Session dialog box to create an event session. However, this wizard is only a subset of the options in the New Session dialog box, so it is not very useful. Therefore, we focus on the new Session dialog box.

To create a extended events session, simply right-click on the Sessions folder and select New Session .... A new Session dialog box with four property pages is displayed, and four property pages are: General,evetns,data storage and advanced, we start with the General page.

Basic Session Properties

On the General Properties page of the session, we can enter a session name. Other options for opening some sessions, such as starting immediately after creation, can be viewed using the show Live view to view event data.
Enter the session name, such as "highreadqueries" to distinguish between the sessions we created in the first order, and the session name must be unique.

Figure 2:the General Properties page of the New Session dialog

If you do not see causality tracking this option may be because the dialog box is too small, this advanced option will no longer be covered in this order. A correct UI design should be to display a drop-down bar in case we can't see the entire screen.

Events page

The next step is to add some events to our session. In this example, we want to add two events sqlserver.sql_statement_completed and sqlserver.rpc_completed. The only thing I like about the events page is that we can search the event library. I bet you've spent a few minutes, hours, and even days looking at the 180 events in the profiler's event list just for the event you need. This is no longer a problem in the extended events UI, and we can search by event name, description, event field, or all conditions.
Enter completed in the Event Library's text box and the dialog will dynamically filter the list, with only 15 events left.

Figure 3:handy Event name filtering when searching the event library

Note The category/channel of these two event classifications, which are based on the Keyword/channel classification of the Windows system events tracing. We will not discuss this in the first order.

In this we will not see all the "_completed" events, in the default settings, the UI will not display the debug channel event. Select the Channel drop-down box and you will see that debug is not selected. If you enable it, you will see more _completed events in the list, and the debug event is for Microsoft internal use in the production environment. Some experts may use them to do advanced troubleshooting, or to better understand how SQL Server works inside. However, Microsoft does not provide support in this way, nor does it guarantee their behavior, and these events may be deleted or changed in the next release.

Double-click the rpc_completed and sql_statement_completed events, they will appear in the selected events panel, and then select the Configure button to add actions and verbs for these events.

Figure 4:adding Events to the event session

This is a point I don't like in the new Session dialog: Configuration events are not in the same panel, you must click the Configure button to switch to the action and predicate options.

Configuring events

There are three tabs in the event configuration options: Global fields (Actions), Filter (predicate) and event fields.

Default Load (Event fields)

The Event Fields tab shows the Events field (data column) of the event's default payload.

Figure 5:the Default payload for an event

Toggle between these two events and you'll see different times the data captured by default is not the same. Most of the fields cannot be removed from this collection, but those with check boxes are optional. For example, in the case of the Pc_completed event, statement is optional, but is collected by default, and the Output_parameters field is optional but is not collected by default.
Do not change the event fields to keep them from collecting the normal, default load of events.

Add actions

Now let's add the actions according to Hugh, and select the Global Fields tab page (acrtions). The Actions page is called a global field because these fields do not belong to any individual event.
Add actions to the check box in the event that only needs to be checked before the column name. To add actions to multiple events, you can select multiple events at the same time on the left. In this example, we need to add Client_app_name, database_id, Server_instance_name, and session_id for both events.

Figure 6:adding Actions to Event

The actions selected above are very friendly in the scene where the fields are collected, providing only the details of the additional events.
Extended events also provides some of the side effects of the actions, called side-effecting actions. For example, Debug_break when an event is triggered, it causes a debug interrupt. Create_dump_all_threads and Create_dump_single_thread each cause SQL Server to create a memory dump for all threads or for a single thread. The database engine executes these actions on the same thread, so they can have a dramatic impact on performance, and we should use them carefully, which we'll discuss further in the next step.

Set Filter (verb)

Finally, we select the filter (predicate) tab to set the verb. In a few exceptional cases, you should configure predicates separately for each event. For "short-circuit" (short-circuit) events, it prevents the extended events engine from collecting unneeded data without unnecessary overhead. The predicate configuration and the "short-circuit" logic are very important, and we'll cover it in more detail in the next step.

In this example, select both events, select Logical_reads from the Filed drop-down box, and the operation Color Lake is greater than or equal, and set 10000 in value. With this configuration, our event sessions capture only those SQL statements or stored procedures that consume more than 10,000 logical reads of the execution process.

Figure 7:adding A predicate

This multi-select feature, in order for each event to be able to add the same predicate, the field drop-down box displays only the fields and global fields that are common to two events. Depending on the selected event, not all fields are optional. For example, if we add the Error_reported event and then select these three events, we will not see Logical_reads, 8 in the predicate option.

Figure 8:adding A predicate to multiple events at the same time

In this example, we set the same predicate for two events, and of course we have the flexibility to set different predicates for each event that cannot be done in SQL Trace.

At this point in the event session configuration, we can choose OK to create the session, because only adding at least one event is a required condition. Specifying a destination file for a session is not a requirement.

Data storage pages: Defining a target

Select the data storage page and select a target of type event_file. At the end of the file name on server text box, select "Browse ..." and select the destination output file path. In 2008 and 2008r2, you need to specify two files, one for log data (such as real event data), and another for metadata (to describe the contents of the file in the log file, so events and actions can be converted correctly). In SQL Server 2012 and later versions, only one file is used.

In SQL Server 2012, the full log folder path is displayed on the UI (for example: C:\Program Files\Microsoft SQL Server\mssql12. Sql2014\mssql\log); only one file name is displayed in SQL Server 2014, but SQL Server uses the Log folder path by default.

The file name has a maximum character length of 260, in our case we use C:\temp\HighReadQueries. You do not need to provide a file name extension (. xel) for the destination file.

The Extended event engine appends _0_ and an integer (the number of milliseconds between file creation time and the 1600-1-1 interval) at the end of the filename to ensure that the file name is always unique. This is very different from SQL, and we have discussed that the trace file name needs to be defined in the trace definition.

The Event_file target provides a maximum file size in megabytes and allows scrolling of file updates. If you do not specify a maximum file size, the file will grow until the hard disk is filled.

Figure 9:selecting and configuring a target

Advanced session Options

In the Advanced page, we can set up additional premium session options. We cover these options in detail in the next step, which is not explained here. In any case, in order to be consistent with the event sessions created in Level 1, we will maintain the default values for these options.

Event Session DDL

Select OK to create the session and it will appear in extended Events | Sessions folder, and the active state, you should check the "Start the event session immediately after session creation" in the general panel. To generate a T-SQL script for an event session, you can select the Script button on the UI before clicking OK, or you can right-select Highreadqueries after the session is created and select "Script Session as" to generate the scripts. In Listing2, I have added a comment to each paragraph of the DDL script that was created by the new session.

/*Create The session, named as specified on the General page*/CREATEEVENT SESSION[highreadqueries]  onSERVER/*Add and configure events, actions and predicates, as specified on the events page*/ADDEVENT sqlserver.rpc_completed (ACTION (Sqlserver.client_app_name, sqlserver.database_id, SQLSERVER.S Erver_instance_name, sqlserver.session_id)WHERE([logical_reads] >=(10000 ) ) ),ADDEVENT sqlserver.sql_statement_completed (ACTION (Sqlserver.client_app_name, sqlserver.database_id, S Qlserver.server_instance_name, sqlserver.session_id)WHERE([logical_reads] >=(10000 ) ) )/*ADD and configure a target, as specified on the Data Storage page*/ADDTARGET Package0.event_file (SETFileName=N'C:\temp\HighReadQueries' )/*Set session-level Options, specified on the general and advanced pages*/ with(max_memory= 4096KB, Event_retention_mode=Allow_single_event_loss, Max_dispatch_latency=  -SECONDS, Max_event_size= 0KB, Memory_partition_mode=NONE, Track_causality= OFF, Startup_state=  on );GO

Listing 2:the Event session DDL for Highreadqueries

This code is almost identical to the Xe_readsfilter_trace session we see in Listing1. In addition, in order to start the session immediately, we also selected "Watch live data on the screen as it is captured". A window in SSMs in which a user displays session data is opened as if it were on the profiler UI. An important enhancement here is that if you view the real-time data overhead that affects system performance, the viewer's connection is automatically disconnected.

Summarize

After completing this step, you should be familiar with creating a basic session in SSMs, where you can capture one or more events, collect a series of actions, use simple predicates, and save the data to a file. These configurations are similar to capturing information in Profiler or trace, but they are not the same.

In the next step, we'll go further into the Extended Events Foundation, allowing you to move from basic data collection to advanced configuration and capture.

SQL Server Extended Events Step 2: Create a basic event session using the UI