SQL Extended Events

In this article, I create a new extended events session by using the newSession dialog box. Defines a self-expanding event, action, and predicate, and publishes a session that collects event data for the purpose.

Start with the UI first

The built-in UI for extended events is introduced after sqlserver2008r2 (excluding 2008r2). Version 2008 can implement extended events by installing plug-ins or by using T-SQL statements. If the SSMs client is 2012, you can also access 2008 of the DB instance, but the extended events UI is not visible. The lack of UI in version 2008 means that you must write T-SQL and XQuery to mine event data. After 2012, the UI is provided to read the target files in version 2008, and the UI can be used for event data analysis.

instance is in SQL Server version 2012, in order to use the extended event in SSMs, first open the Management in the Object Browser | Extended Events, when you open Sessions , you see a list of session times for the current instance. After opening you can see the built-in two extension events, and AlwaysOn_health system_health .

Figure 1

It is important to note that one of the advantages of the extended time is that the event session is saved in the server's metadata, so the default reboot is saved in the instance, but the trace definition is not, and a custom stored procedure must be executed after the reboot to recreate the trace. Extended events are a good solution to these tedious configurations.

First of all, when we build the extended event, we can choose session---write the session script->create to the new Query editing window to get the creation statement of the extended time. This statement is slightly different from the script we wrote ourselves before.

Second, SQL Server contains a number of session-level options at the end of the with script. These options are the default.

create event SESSION [Xe_readsfilter_trace] on Serveradd event sqlserver.rpc_ Completed (ACTION (Sqlserver.client_app_name, sqlserver.database_id, Sqlserver.server_instance_name, Sqls  erver.session_id) where (logical_reads >= 10000)), ADD EVENT sqlserver.sql_statement_completed (ACTION (    Sqlserver.client_app_name, sqlserver.database_id, Sqlserver.server_instance_name, sqlserver.session_id) WHERE (Logical_reads >= 10000)) ADD TARGET package0.event_file (SET filename = ' C:\temp\XE_ReadsFilter_Trace.xe L ', max_file_size = (5), max_rollover_files = (1) ) with (Max_memory = 4096 KB, Event_retention_mode = allow_single_event_loss, max_dispatch_latency = S Econds, max_event_size = 0 KB, Memory_partition_mode = NONE, track_causality = OFF, Startu P_state = OFF) GO 

　　

Code 1

We use the UI to create a new event session, behavior and properties, and output the statement that builds the extended event at the end of the process.

Create a new event session in the UI

You can either use the New Session Wizard or create a new session. However, the Wizards have fewer options and are not recommended for use. So we're focusing on new sessions.

Create a new extended event, just the folder for the mail session, and then select New Session ... The dialog box has four property pages: General, events, data storage, and advanced.

General Page

On the General page, we can enter the event name and turn on some options. Here I enter HighReadQueries as the name, requiring the name to be unique.

Figure 2. General Page

Events Page

The next step is to add some events, switch to the Events page, select the events we want to track and configure them. Set the appropriate actions and predicates.

Add Event

In order to create an event session, you must have at least one event. In the following example, two events were added, sqlserver.sql_statement_completed andsqlserver.rpc_completed。这里可以搜索到所有的事件库，很多我们已经忘记的。在这个分析器中总共有180个时间供你选择，找到需要的。下面再事件库下面的输入框中输入completed ，下面的对话框自动将包含这个单词的事件筛选出来少于15个的事件。

Figure 3

Note the categories /channels of these two event columns, based on the keyword and channel classification system, are used by Windows event tracking. This is not explained in depth, understanding is OK.

Double-click the two events that you want to select, and then they appear in the list on the right: then click the Configure button to add actions and verbs.

Figure 4

Configuring events

Event configuration options consist of three tab, global fields, filters, and event fields.

Default collection (event field)

The event field label shows all the column fields of the event, constituting the default collection field for the event, and some of the more expensive fields are optional to collect or not, such as data_stream ...

Figure 5

Different events have different default capture event data.

Add action

Below we can add the required action for two events in the Global Field tab. Operations are also referred to as global fields, because these fields are not specified by any independent event and are common.

to add an action, you only need to check the selection box. You can also add an action to multiple events, highlight two events, and then select the action. Here we put client_app_name ,, database_id server_instance_name , and session_id add to these two events.

Figure 6

The above operations are generally benign. Extended events also provide an amount of side-effect action. For exampledebug_break字段就会引起调试中断。

Set Filter

Finally, select Filter tab to configure any predicate. To shorten event estimation, prevent the extended events engine from collecting unnecessary data, you need to configure a predicate to filter the event data. predicate configuration and shortening of logic are extremely important.

For example, select two events, then select logical_reads(logical read) from the drop-down list, change the operator column to >=, and enter 10000. In this configuration, our session simply captures sqlstatement or the stored procedure executes more than 10000 of the logical read event.

Figure 7

This choice of multiple functions, adding the same predicate to two events, limits the available global fields and the normal fields of both events. Depending on the time selection, not all fields can be used. For example, join us to joinerror_reported event事件，那么选择这三个事件时，逻辑读这个谓词就不能使用了。如

Figure 8

In this example, we configure the same predicate for two events, but we also have the flexibility to configure each event.

At this point, we can choose OK to create the event session to complete the session because the minimum requirements are met. It is not necessary to define the target of an event session. For example, we configure error_reported event joincreate_dump_single_thread操作，没必要保存抓取数据。下面我们将介绍如何定义目标文件。

Data storage pages: Defining destination Files

Select the data store and then select the type below the target, select event_file. then enter the file name below and choose to browse the output location of the input file. The maximum length of a path is 260 characters. In the example, D: \temp\highreadqueries is used. There is no need to include the default extension for the file. Xel. The extended events engine is appended with _0_ and an integer number (the number of times the file was created to January 1, 1600) as the end of the file, guaranteeing that the file name is unique. This file type provides the option to set the maximum file value, and allows files to be reused multiple times. If you do not specify a maximum file size, the file will grow to fill the drive.

Figure 9

Advanced session Options

On the Advanced Options page, you can see additional advanced session options. Here we can discuss in the future, here to keep the default.

Event Session DDL

Select OK to complete the session, and then the session appears under the folder for the extended events---session, and then finds that he is active because we initially selected the optional box-the event session is opened immediately after the session is created. Generate a T-SQL script, select the Script button click OK or create a script right after creation to the query window (described earlier). Here I have added the corresponding comments in order to understand.

/*create the session, named as specified on the general page*/create EVENT session [Highreadqueries] on Server/*add and Co  Nfigure events, actions and predicates, as specified on the events page*/add EVENT sqlserver.rpc_completed (ACTION (    Sqlserver.client_app_name, sqlserver.database_id, Sqlserver.server_instance_name, sqlserver.session_id) where ([Logical_reads] >= (10000))), ADD EVENT sqlserver.sql_statement_completed (ACTION (sqlserver.client _app_name, sqlserver.database_id, Sqlserver.server_instance_name, sqlserver.session_id) WHERE ([Logica L_reads] >= (10000)))/*add and configure a target, as specified on the Data Storage page*/add target package0.event _file (Set filename = N ' C:\temp\HighReadQueries ')/* Set session-level options, specified on the "and" Advanced Pag  Es*/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 = OFF, startup_state = ON); GO

　　

Code 2

Much like our previous code. In addition to having sessions start immediately, we also selected "capture real-time on-screen monitoring data". A new window is opened to display the data captured by the event session, just as the profiler does. However, when observing real-time data causing system performance problems, the monitor will shut down automatically.

Summarize

Upon completion, we are familiar with extended events such as creating a basic session, capturing one or more event information, collecting the number of operations, using simple predicates, writing files, and so on. Visual operation greatly reduces the difficulty of the script, but also provides a lot of basic information selection, greatly facilitates the development of extended events. This is just the beginning of the application, I hope the great God can point out.

SQL Extended Events