New In SQL Server 2022 and Azure SQL: XEvent Performance Metrics

[Guest MitchellSternke]

Running Extended Event (XEvent) sessions on SQL Server has a cost. Since XEvents was designed for high-performance, this is usually unnoticeable. However, it can become an issue depending on which events, actions, and other XEvent features are in use. New metrics available in SQL Server 2022, in Azure SQL Database, and in Azure SQL Managed Instance can help you understand the performance impact of using XEvents in your database.

 

 

 

The sys.dm_xe_session_events DMV (sys.dm_xe_ database_session_events on Azure SQL Database) provides one row for each event found in an active session definition, allowing you to see all events that are currently publishing on your SQL Server instance. Four new columns have been added to help with troubleshooting performance:

 

1. event_fire_count: number of times that the event has fired (published) since the session started
   
2. event_fire_average_time: the average (mean) time taken to publish the event, in microseconds
   
3. event_fire_min_time: the minimum time taken to publish the event, in microseconds
   
4. event_fire_max_time: the maximum time taken to publish the event, in microseconds
   

 

Columns event_fire_min_time and event_fire_max_time are enabled by default. Columns event_fire_count and event_fire_average_time can be enabled with SQL Server global trace flag 9708 when you start SQL Server or with the DBCC TRACEON command. These additional columns can impact SQL Server performance, so it is recommended to only enable them during investigation.

 

 

 

You can join sys.dm_xe_session_events with sys.dm_xe_sessions (sys.dm_xe_database_sessions on Azure SQL Database) on the session address column to see the session name that each event belongs to. Below are example queries that you can modify to investigate performance.

 

 

 

-- Example: Querying event metrics for the built-in system_health session (SQL Server and Azure SQL Managed Instance)

DECLARE @SessionName AS NVARCHAR(256) = 'system_health';

SELECT s.name AS session_name, e.event_name, e.event_fire_count, e.event_fire_average_time, e.event_fire_min_time, e.event_fire_max_time

FROM sys.dm_xe_sessions s

INNER JOIN sys.dm_xe_session_events e ON s.address = e.event_session_address

WHERE s.name = @SessionName;

 

 

 

 

 

-- Example: Querying event metrics for a user session named 'ExampleSession' (Azure SQL Database)

DECLARE @SessionName AS NVARCHAR(256) = 'ExampleSession';

SELECT s.name AS session_name, e.event_name, e.event_fire_count, e.event_fire_average_time, e.event_fire_min_time, e.event_fire_max_time

FROM sys.dm_xe_database_sessions s

INNER JOIN sys.dm_xe_database_session_events e ON s.address = e.event_session_address

WHERE s.name = @SessionName;

 

 

 

Example output:

 

 

 

 

You can look at event_fire_max_time/event_fire_average_time columns to identify events using the most CPU time. If you find an event with a large time value (say, more than 1000 microseconds), then take a look at the session definition (tip: use the Script Session as ... Create to feature in SSMS to view it as T-SQL). Check if there are any actions or predicates attached to the event that could explain the CPU usage. For example, capturing the sql_text action can be costly with large T-SQL queries. You can experiment by changing your session definition with ALTER EVENT SESSION syntax or by using the Extended Events Wizard in SSMS.

 

Continue reading...