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.
At the same time, we can find the same option on the Extended Events 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.
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.
After clicking the OK button, the sum of the physical reads for each query will be shown.
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
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.
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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 |
CREATE EVENT SESSION [CaptureQuery_MultipleEventLoss] ON SERVER ADD EVENT sqlserver.lock_acquired, ADD EVENT sqlserver.lock_released, ADD EVENT sqlserver.lock_request_priority_state, ADD EVENT sqlserver.query_pre_execution_showplan, ADD EVENT sqlserver.sql_statement_completed, ADD EVENT sqlserver.sql_statement_starting ADD TARGET package0.event_file(SET filename=N'C:\CapturedEvents\CaptureQuery_MultipleEventLoss.xel') WITH (MAX_MEMORY=1024 KB,EVENT_RETENTION_MODE=ALLOW_MULTIPLE_EVENT_LOSS, MAX_DISPATCH_LATENCY=30 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE, TRACK_CAUSALITY=OFF,STARTUP_STATE=OFF) GO ALTER EVENT SESSION CaptureQuery_MultipleEventLoss ON SERVER STATE = START GO CREATE EVENT SESSION CaptureQuery_SingleEventLoss ON SERVER ADD EVENT sqlserver.lock_acquired, ADD EVENT sqlserver.lock_released, ADD EVENT sqlserver.lock_request_priority_state, ADD EVENT sqlserver.query_pre_execution_showplan, ADD EVENT sqlserver.sql_statement_completed, ADD EVENT sqlserver.sql_statement_starting ADD TARGET package0.event_file(SET filename=N'C:\CapturedEvents\CaptureQuery_MultipleEventLoss.xel') WITH (MAX_MEMORY=1024 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=OFF) GO ALTER EVENT SESSION CaptureQuery_SingleEventLoss ON SERVER STATE = START |
After creating the event session we will create a little workload on the Adventureworks database.
- Note: You can see this Performing a Load Test on SQL Server using Apache JMeter article to generate a workload on the Adventureworks database
Now we execute the following query to see how many events and buffers have been lost by these sessions.
|
1 2 3 4 5 6 7 8 9 10 11 |
SELECT xe.name, xe.total_buffer_size, xe.total_bytes_generated, xe.buffer_policy_desc, xe.buffer_processed_count, xe.dropped_event_count, xe.dropped_buffer_count , xe.blocked_event_fire_time, xe.largest_event_dropped_size FROM sys.dm_xe_sessions AS xe |
- 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.
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.
After this step, we will create an upper group with the object name to involve the object name into the grouped filter.
Now, we will execute the uspGetBillOfMaterials stored procedure in different databases on a query window that has a session-id equal to 83.
When we check the captured events data, we will only see one event that is performed in the Adventureworks2019 database.
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.
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.
After setting the destination table, the event data will be exported into it.
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.
We give a name to the exported file and set the file location for it.
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.
|
1 2 |
SELECT CAST(event_data AS XML) AS event_data FROM sys.fn_xe_file_target_read_file( 'C:\CapturedEvents\NewExtendedEvent_0.xel', NULL, NULL, NULL ) |
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.
Most of his career has been focused on SQL Server Database Administration and Development. His current interests are in database administration and Business Intelligence. You can find him on LinkedIn.
View all posts by Esat Erkec
- SQL Performance Tuning tips for newbies - April 15, 2024
- SQL Unit Testing reference guide for beginners - August 11, 2023
- SQL Cheat Sheet for Newbies - February 21, 2023