SQL Server Extended Events

SQL Server Extended Events (Extended event) is a general event processing system for servers, an artifact that tracks the running state of a SQL Server system, and a logging tool that can completely replace SQL Trace (SQL Trace). Design features for extended events:

• Because the extended event engine does not recognize events, the engine can bind any event to any target because the engine is not constrained by the event content.
• The event is different from the event consumer, which is called the target in the extended event, which means that any target can receive any event. In addition, any events that are raised can be used automatically by the target, which can record or provide additional event contexts.
• The event differs from the action to be performed when the event fires. Therefore, any action can be associated with any event.
• Predicates can dynamically filter the firing of events, thereby enhancing the flexibility of the extended event infrastructure.

Extended events can generate event data synchronously (and process the data asynchronously), which provides a flexible solution for event handling. In addition, extended events provide the following features:

• A unified approach for handling events across server systems, while enabling users to isolate specific events for troubleshooting.
• Integrates with existing ETW tools and supports existing ETW tools.
• Fully configurable event handling mechanism based on Transact-SQL.
• Active processes can be dynamically monitored while minimizing the impact on those processes.

The process of creating extended events using TSQL is complicated, but we can use another simple method: Using the Create Wizard with extended time.

One, create the session for the extended event

Step1, open the new Session Wizard

Step2, setting session properties (Set sessions property)

Step3, select templates (Choose template)

STEP4, option capture event (Select events to capture)

The tab is divided into two pane, the left pane is used to search for the event, in the event library, enter the event name that needs to be traced, and after selecting an event, the wizard will display the event's description and event Fields,event fields is the field that describes the Event output. The right pane is used to list the events that have been selected.

STEP5, select the Captured field (Capture Global fields)

Select other information when the event occurs, which is usually related to the session, such as database_id and database_id, which is the Database information for event occurrence. Sql_text is also selected for easy access to the SQL statement that triggered the event.

STEP6, set session time filter (set sessions Event Filters)

STEP7, specifying the storage of Session data (specify session, Storage)

Set the target for the output data store, which lists the Event File target and the ring buffer target.

The Event file target uses file to store session Output, which is used when a large number of datasets need to be stored.

The Ring buffer target uses memory buffer to store session Output, and if the allocated memory buffer is exhausted, Target will delete the oldest events to accommodate the new events so that memory Most recent data is stored in the buffers.

STEP8, review the summary information for the extended events session and start creating the event session.

Second, view the messages captured by the extended event

The SQL Server extended event capture message, called Target, uses the target to store Events,target to store the captured message in a file (the extension is. xel), or Memoy buffer (Ring buffer), Target is able to process the data synchronously or asynchronously, and the event data is stored in XML format.

Three, storing target data with a file

Target data generated by the extended event is always in memory buffer, until memory buffers accumulates enough data, and then writes all the data in memory to the file. The data in the file lags in memory buffer, which is asynchronous write (async write), which reduces the number of IO and improves IO efficiency. The taget extension of the event file type is Xel, which stores the target data in XML format and uses the Sys.fn_xe_file_target_read_file function to view the data stored in the event file.

1, reusing event files in rollover mode

The target of the event file type rollover The file, for example, if max_rollover_files=3, the system retains a maximum of 3 xel files, the file name: Xxx_0.xel,xxx_1.xel,xxx_2.xel. When the file xxx_0.xel reaches Max_file_size, Target executes the rollover once, deleting the file: Xxx_0.xel, creating a new file: Xxx_3.xel, and storing the event data with the file.

2. View the storage path of the event file from Sys.dm_xe_session_targets

 select  s.name as   Xe_session_name,  cast  (st.target_data  As  XML) as   Target_data  from   sys.dm_xe_sessions s  inner  join   Sys.dm_xe_session_targets St  on  s.address=  st.event_session_address  where  s.name=    xe_session_name    

3. Check the output of target from the Xel file

SELECT * CAST  as  as ' Event_data_xml '  from Sys.fn_xe_file_target_read_file ('file_name*.xel'NULL NULL NULL)

Four, Ring Buffer Target

The Ring buffer target saves the event data to memory and the event data is stored in XML format. Once the event data buffers the allocated memory, the oldest event data is erased.

1, storage mode

The ring buffer target simply stores the data in memory, which can be used to manage events in two modes:

• The first pattern is strictly FIFO (first-in first-out, FIFO), that is, when the allocated memory is exhausted by Target, the oldest event of creation time is removed from memory.
• The second mode is per-event FIFO mode, which means that each type of event holds a count. In this mode, when the allocated memory is exhausted by Target, the oldest event of the creation time in each type is removed from memory.

2. View the target output from the Sys.dm_xe_session_targets

SELECT CAST  as XML)  from  as Xet JOIN  as XE     on = xet.event_session_address) WHERE = ' Session_name '

Five, use SSMs to view target data

The target output obtained through the TSQL script is displayed in XML format, and the output of target can be viewed in tabular form through view target data.

Six, query session and Target

1. View the session configuration for extended events

Select s.name,    s.total_regular_buffers*s.regular_buffer_size/1024x768 as total_regular_buffer_kb,    s.total_buffer_size/1024x768 as Total_ buffer_kb,    S.buffer_policy_desc,    s.flag_desc,    s.dropped_event_count,    s.dropped_buffer_ Count from Sys.dm_xe_sessions s

Field Notes:

Regular Buffer: Most of the time, the extended events session uses a regular buffer, which is large enough to store information about many events. In particular, each extended event session will have three or more buffer. The allocation of regular buffer is determined by SQL Server, and SQL Server sets the memory partition based on the Memory_partition_mode option, with the same settings for the size and max_memory options in normal buffer.

Buffer_policy_desc: Used to describe how the extended event session handles the newly triggered event when buffer is exhausted:

• Drop Event
• Do not drop events
• Drop Full Buffer
• Allocate New Buffer

2. View the configuration of the session target

Select  as Xe_session_name,    st.target_name,    st.execution_count,    st.execution_duration_ms/  as Avg_execution_ms,    st.target_datafrominnerjoin  Sys.dm_xe_sessions s     on st.event_session_address=s.address

When the name of target is an event file, the data that is output by the Session is actually stored in the event file.

When the name of target is ring buffer, the Session output data is stored in memory buffers, which is viewed through the Sys.dm_xe_session_targets target_data field.

Reference Documentation:

SQL Server Extended Events Targets

SYS.DM_XE_SESSIONS (Transact-SQL)

SYS.DM_XE_SESSION_TARGETS (Transact-SQL)

SYS.FN_XE_FILE_TARGET_READ_FILE (Transact-SQL)

How to Query Extended Events Target XML

Realistic troubleshooting example of extended events (XEvent) usage in SQL Server 2008–part 1

Using Xquery to query Extended Events asynchronous file target results

SQL Server Extended Events