SQL Server Extended Events: A Practical Guide to Sessions, Targets and Analysis

When a performance problem or an unexpected error occurs, the most valuable information is what was happening on the server at that exact moment. Which query ran for how long, which application hit an error, who was blocking whom? Once the problem has passed, DMVs can’t answer most of these questions. Extended Events (XE) is SQL Server’s built-in tracing infrastructure that captures this information as it happens, selectively and at low cost.

This guide explains how Extended Events works, how session settings affect performance, which target to use when, and the sessions that are most useful in production, with ready-to-use scripts.

What Extended Events Is, and Why Not Profiler

Extended Events is the event-based tracing system introduced in SQL Server 2008 and extended in every release since. It captures events defined at hundreds of points inside the database engine, enriches them with additional information, filters them by conditions and writes them to a target you choose.

Microsoft has deprecated SQL Trace and SQL Server Profiler for the database engine and recommends Extended Events instead. There are two practical reasons. First, the events in Profiler stopped at the SQL Server 2008 R2 level; events for Always On, columnstore, Query Store or newer wait types exist only in Extended Events. Second, Extended Events sessions typically consume far fewer resources than SQL Trace. Connecting Profiler remotely to a production server and collecting an unfiltered trace is still a common habit, and it can become a performance problem in its own right.

Core Concepts

An Extended Events session is built from five pieces:

The most important detail for performance is the order of processing. When an event fires, the predicate is evaluated first; an event that fails the condition is dropped before any actions are collected or anything is written to a target. A well-designed session therefore uses filters that are as selective as possible and adds expensive actions such as sql_text only to the events that really need them.

Start With What You Already Have: the system_health Session

The system_health session runs by default on every SQL Server installation and has often recorded the answer before you configure anything. According to Microsoft’s documentation, it collects:

• Every detected deadlock, including the deadlock graph.
• Errors with severity 20 or higher, and memory-related errors such as 701, 802 and 8645.
• Lock waits longer than 30 seconds and latch waits longer than 15 seconds.
• Non-yielding scheduler problems, sp_server_diagnostics component results, and connectivity and security errors.

The session writes to both a ring_buffer and an event_file target. The file target is limited to 4 files of 5 MB by default; in Standard and Enterprise editions the limit is 10 files of 100 MB. Microsoft recommends that you don’t stop, alter or delete this session, and notes that changes may be overwritten by a future update.

For example, you can read recent deadlocks straight from the system_health files without setting up a separate session:

SELECT
    xf.timestamp_utc,
    CAST(xf.event_data AS xml).query('(event/data[@name="xml_report"]/value/deadlock)[1]') AS deadlock_graph
FROM sys.fn_xe_file_target_read_file(N'system_health*.xel', NULL, NULL, NULL) AS xf
WHERE xf.object_name = N'xml_deadlock_report'
ORDER BY xf.timestamp_utc DESC;

Save the resulting XML with an .xdl extension and open it in SSMS to see the deadlock graph visually. We cover deadlock analysis in detail in our SQL Server deadlock guide. In Always On environments, the built-in AlwaysOn_health session similarly records replica state changes and failover events.

Session Settings and Their Performance Impact

A session’s impact on the server depends as much on its session-level settings as on the events it captures:

• MAX_MEMORY: the total size of the buffers that hold events before they are dispatched to targets. The default is 4 MB, which is enough for most sessions.
• EVENT_RETENTION_MODE: what happens when the buffers fill up. The default, ALLOW_SINGLE_EVENT_LOSS, allows individual events to be dropped when all buffers are full, which keeps the performance impact to a minimum. ALLOW_MULTIPLE_EVENT_LOSS suits very busy sessions.
• MAX_DISPATCH_LATENCY: how long events may stay in memory. The default is 30 seconds; it is usually the reason events appear late while watching live data.
• STARTUP_STATE: when ON, the session starts automatically every time SQL Server starts. Always use it for sessions that should run continuously.
• TRACK_CAUSALITY: adds an identifier that correlates related events, which makes it easier to follow a batch together with its statements.
• MAX_DURATION: introduced in SQL Server 2025, this setting stops the session automatically after the specified time. It’s the cleanest way to stop temporary diagnostic sessions from being forgotten and running for weeks.

Which Target, When?

Four Essential Sessions for Production

The sessions below are the ones that pay off most when getting to know an environment and catching problems early, and when properly filtered they can be left running permanently. Adjust the file paths and thresholds to your environment.

1. Long-running queries

Captures stored procedure calls from applications (rpc_completed) and ad hoc batches (sql_batch_completed) with a 5-second threshold, excluding system sessions:

CREATE EVENT SESSION [LongRunningQueries] ON SERVER
ADD EVENT sqlserver.rpc_completed (
    ACTION (sqlserver.client_app_name, sqlserver.client_hostname,
            sqlserver.database_name, sqlserver.username, sqlserver.query_hash)
    WHERE (sqlserver.is_system = 0 AND duration >= 5000000)
),
ADD EVENT sqlserver.sql_batch_completed (
    ACTION (sqlserver.client_app_name, sqlserver.client_hostname,
            sqlserver.database_name, sqlserver.username, sqlserver.query_hash)
    WHERE (sqlserver.is_system = 0 AND duration >= 5000000)
)
ADD TARGET package0.event_file (
    SET filename = N'E:\XE\LongRunningQueries.xel', max_file_size = 100, max_rollover_files = 5
)
WITH (MAX_MEMORY = 4 MB, EVENT_RETENTION_MODE = ALLOW_SINGLE_EVENT_LOSS,
      MAX_DISPATCH_LATENCY = 30 SECONDS, STARTUP_STATE = ON);
GO
ALTER EVENT SESSION [LongRunningQueries] ON SERVER STATE = START;

The query_hash action lets you group queries that share the same shape even when their parameters differ, so you can tell a one-off slowdown apart from a problem that repeats hundreds of times a day.

2. Errors and timeouts

Most “we get errors now and then” complaints are problems that show up as a generic message in application logs but map to a specific error number in the database. The attention event captures cases where the client cancelled a query or it timed out:

CREATE EVENT SESSION [ErrorsAndTimeouts] ON SERVER
ADD EVENT sqlserver.error_reported (
    ACTION (sqlserver.client_app_name, sqlserver.client_hostname,
            sqlserver.database_name, sqlserver.username, sqlserver.sql_text)
    WHERE (severity >= 16 AND sqlserver.is_system = 0)
),
ADD EVENT sqlserver.attention (
    ACTION (sqlserver.client_app_name, sqlserver.client_hostname,
            sqlserver.database_name, sqlserver.username, sqlserver.sql_text)
)
ADD TARGET package0.event_file (
    SET filename = N'E:\XE\ErrorsAndTimeouts.xel', max_file_size = 50, max_rollover_files = 5
)
WITH (STARTUP_STATE = ON);
GO
ALTER EVENT SESSION [ErrorsAndTimeouts] ON SERVER STATE = START;

3. Blocking

Unlike a deadlock, blocking doesn’t resolve itself, and system_health only records lock waits longer than 30 seconds. For earlier and more detailed information, first set the blocked process threshold option in seconds. It is disabled by default, the minimum is 5 seconds, and because the lock monitor only wakes up every 5 seconds the report is produced on a best-effort basis rather than in real time:

EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;
EXEC sp_configure 'blocked process threshold', 20;
RECONFIGURE;
GO
CREATE EVENT SESSION [Blocking] ON SERVER
ADD EVENT sqlserver.blocked_process_report
ADD TARGET package0.event_file (
    SET filename = N'E:\XE\Blocking.xel', max_file_size = 50, max_rollover_files = 5
)
WITH (STARTUP_STATE = ON);
GO
ALTER EVENT SESSION [Blocking] ON SERVER STATE = START;

Each report contains the query text, login details and waited-on resource for both the blocked and the blocking session. Reviewing the reports in time order is usually enough to find the session at the head of a blocking chain.

4. A histogram for distributions

Sometimes you need the distribution of events rather than each event’s detail. To see which errors occur most often on a server, for example, a histogram that counts by error number is enough instead of writing every error event to a file. This session takes up almost no disk space:

CREATE EVENT SESSION [ErrorDistribution] ON SERVER
ADD EVENT sqlserver.error_reported (
    WHERE (severity >= 11 AND sqlserver.is_system = 0)
)
ADD TARGET package0.histogram (
    SET filtering_event_name = N'sqlserver.error_reported',
        source = N'error_number',
        source_type = 0,
        slots = 1024
);
GO
ALTER EVENT SESSION [ErrorDistribution] ON SERVER STATE = START;

Reading the Collected Data

For quick checks, right-click a session in SSMS and use Watch Live Data, or View Target Data on a file target. For deeper analysis and reporting, read the files from T-SQL with sys.fn_xe_file_target_read_file and shred the XML into columns. From SQL Server 2017 onwards the function also returns a timestamp_utc column, and filtering on it is much faster than parsing the XML of every event:

WITH events AS (
    SELECT xf.timestamp_utc, CAST(xf.event_data AS xml) AS ed
    FROM sys.fn_xe_file_target_read_file(N'E:\XE\LongRunningQueries*.xel', NULL, NULL, NULL) AS xf
    WHERE xf.timestamp_utc >= DATEADD(HOUR, -24, SYSUTCDATETIME())
)
SELECT
    timestamp_utc,
    ed.value('(event/@name)[1]', 'sysname') AS event_name,
    ed.value('(event/data[@name="duration"]/value)[1]', 'bigint') / 1000 AS duration_ms,
    ed.value('(event/data[@name="cpu_time"]/value)[1]', 'bigint') / 1000 AS cpu_ms,
    ed.value('(event/data[@name="logical_reads"]/value)[1]', 'bigint') AS logical_reads,
    ed.value('(event/action[@name="database_name"]/value)[1]', 'sysname') AS database_name,
    ed.value('(event/action[@name="client_app_name"]/value)[1]', 'nvarchar(256)') AS app_name,
    ed.value('(event/action[@name="username"]/value)[1]', 'nvarchar(256)') AS user_name,
    COALESCE(ed.value('(event/data[@name="statement"]/value)[1]', 'nvarchar(max)'),
             ed.value('(event/data[@name="batch_text"]/value)[1]', 'nvarchar(max)')) AS query_text
FROM events
ORDER BY duration_ms DESC;

Using this function requires VIEW SERVER STATE on SQL Server 2019 and earlier, and VIEW SERVER PERFORMANCE STATE on SQL Server 2022 and later. XML shredding is CPU-intensive on large files; rather than running it on the production server during busy hours, we recommend copying the files to another server and analyzing them there.

Keeping an Eye on Running Sessions

A common problem with Extended Events is sessions that were started to investigate something and then forgotten. A session that captures every lock acquisition or every query plan keeps consuming resources silently for weeks once it’s forgotten. Check regularly which sessions are running, how much memory they use and whether they are losing events:

SELECT
    s.name AS session_name,
    s.create_time,
    s.total_buffer_size / 1024 AS buffer_kb,
    s.dropped_event_count,
    s.dropped_buffer_count,
    t.target_name
FROM sys.dm_xe_sessions AS s
JOIN sys.dm_xe_session_targets AS t ON t.event_session_address = s.address
ORDER BY s.create_time;

A steadily increasing dropped_event_count means the session’s event volume doesn’t fit in its buffers. Narrowing the filter is usually a better fix than enlarging the buffers.

Extended Events vs. SQL Server Audit

Both features share the same infrastructure, but their purposes differ. Extended Events is for diagnostics and performance analysis; sessions are flexible, often temporary and designed to tolerate event loss. When you need a complete, evidential record of who did what and when for security and compliance, use SQL Server Audit. Audit offers features built for that purpose, such as failure behaviors that decide what happens when a record can’t be written and the ability to record changes to itself.

Checklist

• The system_health session is running and unmodified.
• Permanent sessions for long-running queries, errors and blocking are defined with STARTUP_STATE = ON.
• Every event_file target has a size and file-count limit; files live in a folder separate from data disks, with restricted permissions.
• Duration filters are written correctly in microseconds.
• NO_EVENT_LOSS and plan-capturing events aren’t used permanently in production.
• Running sessions and event loss are reviewed regularly.