Esat Erkec
Export event data to XEL file

SQL Server Extended Events tips

November 17, 2021 by

This article aims to provide some beneficial tips about SQL Server extended events that make it easier to create and use event sessions.

Introduction

SQL Server extended events is a performance monitoring tool that has the ability to collect various event data of the SQL Server. So that, it allows diagnosing the performance and other issues more easily. At the same time, using the SQL Server extended events provide us the following main advantages :

  • Advanced graphical user interface
  • Low resource usage and minimal performance impact on the database engine
  • Possibility to collect numerous events data

Grouping, Sorting and Aggregate event data in the Data Explorer

SQL Server extended events offer a data explorer tool that enables to view, filter, and grouping the captured data. With the help of the grouping functionality, we can arrange the rows of the collected data into groups, and we can also apply the aggregation functions (SUM, COUNT, MAX, MIN, AVG) to these fields. Assume that, we have collected executed queries data from a server and we want to detect which queries make the most physical reads. As the first step, we click the Grouping button on the toolbar.

SQL Server extended event collected data

At the same time, we can find the same option on the Extended Events menu.

Extended Event grouping menu

After clicking the Grouping button a window will appear and we can select the fields that we want to group. In this window, we select only the statement field from the Available columns list and send it into the Columns grouped on list. So the captured data will be grouped according to each query. Also, the number of the queries each group contains will be shown in parentheses next to the query text.

Extended event data aggregation

As we can see, this data view does not help us to make meaningful performance analysis, solely we can see how many times a query has been executed. We can find the Aggregation button on the toolbar and we can calculate different aggregation types for the non-grouped fields. After clicking the button, the Aggregation window will appear. In this window, we will select the SUM aggregation type for the physical_reads fields. So that, the total physical read values will be shown for each statement group. And finally, we will sort each grouped query statement in descending order.

Aggregation window of the extended event

After clicking the OK button, the sum of the physical reads for each query will be shown.

Summarize the event data

The system_health event session

The system_health session is the default extended event session of the SQL Server and it starts automatically when the database engine starts. We can use this event to monitor the following issues:

  • Deadlocks
  • Latch waits
  • Lock waits
  • Connectivity errors
  • Security errors
  • Errors with severity

Extended event system_health

SQL Server Extended Events EVENT_RETENTION_MODE option

SQL Server reserves buffer memory for the event sessions to store the event data temporarily and then dispatch them to the target storage. In some cases, the extended event sessions generate data incredibly fast. However, the collected event data can not be dispatched to the event storage as fast as the event data generation speed. To overcome these types of cases, the EVENT_RETENTION_MODE setting allows tolerance to be set for data loss when the extended event is under pressure. So that, the event session impact on the database engine performance is minimized.

ALLOW_SINGLE_EVENT_LOSS: This option is the default option and allows single events to be dropped and lost from the session when the event Session memory buffer is full and can not be sent to the storage target.

ALLOW_MULTIPLE_EVENT_LOSS: This option affects the performance minimally and allows the entire memory buffer containing multiple events to be dropped and lost when the memory buffers are full.

NO_EVENT_LOSS: When we use this option, we can ensure that no event data can be lost but it causes to degrade database engine performance.

Extended Events EVENT_RETENTION_MODE option

We can obtain information about the active event sessions, with the help of the sys.dm_xe_sessions dynamic management view. At the same time, this view returns information about the dropped event and buffer counts. To understand this concept more clearly, we will create two different extended event sessions. The first one is retention mode is Multiple event loss and the second one is Single event loss. These events capture the locks and SQL statement executions.

After creating the event session we will create a little workload on the Adventureworks database.

Now we execute the following query to see how many events and buffers have been lost by these sessions.

dm_xe_sessions view explanation

  • name: Name of the event session
  • total_buffer_size: The total amount of the buffer size that is used to store event data in the buffer
  • total_bytes_generated: The total amount generated data size during collecting the data
  • buffer_policy_desc: This column identifies the setting of the EVENT_RETENTION_MODE
  • dropped_event_count: Number of the dropped events when the event buffers were full
  • dropped_buffers_count: Number of the dropped event buffers when the event buffers were full

Advanced Filtering

SQL Server extended event allows to filter the collected data but sometimes we require advanced filtering options. Assume that, we have a stored procedure and we want to capture when this procedure is executed by the specific session id on the particular databases. At this point, we need to group the filter field clauses and then separate them with the OR condition.

extended event group filtering

First of all, we will use the sp_statement_completed event to capture when a stored procedure has been completed. As a second step, we need to group the database names and sessions id’s in the filter tab to generate a grouped condition that can work together. To do this, we select the two fields together and then right-click on these fields and select the Group Clauses option.

Grouping the extended event filters

After this step, we will create an upper group with the object name to involve the object name into the grouped filter.

Using group clauses in the extended event

Now, we will execute the uspGetBillOfMaterials stored procedure in different databases on a query window that has a session-id equal to 83.

Test the extended event

When we check the captured events data, we will only see one event that is performed in the Adventureworks2019 database.

Usage details of the sp_statement_completed

Exporting the SQL Server extended event captured data

We can export the collected data by the SQL Server extended events to the different file types or an SQL table. Comma Separated Values (CSV) and SQL Server Extended Event File (XEL) are the two file types that we can export the collected event data. To export an event data into a SQL table, we open the Extended Events menu on the SQL Server Management Studio (SSMS) and then choose the Table option of the Export to sub-menu.

Export event data

In the second step, we need to set destination table settings. We can export the event data into an existing table or create a new table. In this example, we will give a new table name and export the data into this table.

Choose destination table

After setting the destination table, the event data will be exported into it.

Destination table details

XEL file type is another option to export event data. We can export the event data into an XEL file and can store it location of the local file system. It is enough to select the XEL File… export option in the Export to sub-menu.

Export event data to XEL file

We give a name to the exported file and set the file location for it.

Save the XEL data

With the help of the fn_xe_file_target_read_file function , we can read XEL files. However, this function returns the data in the XML format.

How to read XEL data of the SQL Server extended event

Conclusion

In this article, we have learned how to use SQL Server extended events effectively. Extended Events is a diagnostic tool of SQL Server to detect the performance and other issues on the database engine.

Esat Erkec
168 Views