SQL Server Extended Events: The Definitive Guide for DBAs

Extended Events replaced SQL Trace over a decade ago. Most DBAs still default to legacy tools when diagnosing production issues — missing the speed, flexibility, and diagnostic depth that XEvents actually provide. Here is the guide I wish had existed when I made the switch.

---

If you are still firing up SQL Profiler to debug production issues, you are working with a tool that was formally deprecated in SQL Server 2012 and has been limping along in maintenance mode ever since. Extended Events shipped in SQL Server 2008 as the replacement. By SQL Server 2012, it was the recommended path. By now, after nearly two decades in production, XEvents are mature, well-documented, and dramatically faster than anything in the legacy Trace stack.

That is the context. Now the practical question: what does Extended Events give you that SQL Trace does not, and how do you actually use it without spending a week reading documentation first?

What Extended Events Actually Is

Extended Events is a lightweight, highly extensible event collection framework built into the SQL Server engine. Where SQL Trace used a separate Windows service with significant per-event overhead, XEvents run as an in-process ring buffer or async target with overhead measured in microseconds per event. You can capture thousands of events per second in production with negligible performance impact — a claim SQL Profiler simply cannot make.

The architecture has three moving parts you need to understand:

• Events — individual things that happen inside the engine (error\_reported, sql\_statement\_completed, lock\_deadlock\_chain, blocked\_process\_report, etc.). There are hundreds of them; only a handful matter day-to-day.
• Targets — where captured events go. event_file writes to an .xel file on disk. ring_buffer stores events in memory (wiped on server restart). histogram aggregates event counts by a discriminator field. pair_matching captures correlated events (e.g., batch start and batch end). Choose your target based on your retention and retrieval needs.
• Predicates and Actions — Predicates filter events at source (e.g., only capture errors above severity 16). Actions enrich events with additional context (e.g., attach the full SQL text, the execution plan, or the session's SPID details).

5 Practical Use Cases

Free · 2 Minutes

How healthy is your database, really?

Get your free database health score — spot risks before they become incidents.

Get my health score

1. Deadlock Detection and Graph Capture

The deadlock graph is the single most useful artifact in any deadlock investigation. SQL Trace required Trace Flag 1222 or 1204 to dump the graph to the error log — but you still had to go fishing through a text blob that looked like it was formatted for a printer from 1985. Extended Events captures the deadlock graph as XML in a dedicated event (xml_deadlock_report) and lets you attach it directly to the event payload.

When a deadlock occurs, you get a clean XML graph showing every victim, every resource, every lock mode, and the exact call stack of each session involved — all in one self-contained document you can open in SSMS's deadlock visualization viewer or in any browser.

2. Query Performance Monitoring

The sql_statement_completed and sql_batch_completed events let you capture execution statistics for every query — duration, CPU time, row count, logical reads — without the overhead of a Profiler trace. Use predicates to filter by duration threshold (e.g., only capture queries running longer than 1 second) so you are not overwhelmed by the noise from fast queries.

Combined with the sqlserver.sql_text action, you get per-query stats that are directly comparable to what Query Store reports — and you can tune the collection window to capture a specific time period (peak hours, a batch window, after a deployment) rather than relying on historical data that may have already rolled out of Query Store's retention window.

3. Blocking Chain Identification

The blocked_process_report event fires when a process is blocked for longer than a configured threshold. Set the threshold with the blocked_process_threshold server configuration option (in seconds). When the event fires, it captures the full blocking chain — who is blocking, what they are blocking, the wait type, and the first few characters of the blocking query text.

This is more useful than sys.dm_exec_requests snapshots because it fires at the moment the threshold is exceeded rather than requiring you to poll repeatedly and hope you catch it.

4. Error and Exception Tracking

The error_reported event captures every error that fires inside the engine, with severity, state, and the full error message. You can predicate on severity (e.g., capture severity 16 and above — user errors worth acting on) so you capture application-level errors without drowning in informational messages from the engine.

Useful for tracking specific error codes over time, monitoring for new error patterns after deployments, and building baselines of how often specific errors occur in a given window.

5. Query Timeout Detection

When a command times out from the client side (ADO.NET CommandTimeout exceeded, for example), SQL Server does not log it by default — you just see a connection interrupted or a generic timeout message. The attention event fires when a query is cancelled externally. Capture this with the sqlserver.session_id and sqlserver.sql_text actions to understand which query was running when the timeout occurred, how long it had been running, and what was in the batch.

4 Copy-Paste T-SQL Examples

Example 1: Deadlock Graph Capture with event\_file Target

-- Create a session to capture deadlock graphs as .xel files on disk
-- Run in a query window (SSMS) against the target instance
-- Replace 'C:\XE\Deadlocks\' with a path that exists and SQL Server service account can write to

CREATE EVENT SESSION [DeadlockCapture_event_file]
ON SERVER
ADD EVENT sqlserver.xml_deadlock_report
(
    ACTION
    (
        sqlserver.sql_text,
        sqlserver.session_id,
        sqlserver.database_id,
        sqlserver.tsql_stack,
        package0.event_sequence
    )
)
ADD TARGET package0.event_file
(
    SET filename = 'C:\XE\Deadlocks\DeadlockCapture.xel',
        max_file_size = 50,
        max_rollover_files = 10
)
WITH
(
    MAX_DISPATCH_LATENCY = 5 SECONDS,
    STARTUP_STATE = OFF
);

ALTER EVENT SESSION [DeadlockCapture_event_file]
ON SERVER
STATE = START;

SELECT
    object_name AS event_type,
    CONVERT(xml, event_data) AS deadlock_xml,
    timestamp
FROM sys.fn_xe_file_target_read_file(
    'C:\XE\Deadlocks\DeadlockCapture*.xel',
    NULL, NULL, NULL
)
WHERE object_name = 'xml_deadlock_report';

ALTER EVENT SESSION [DeadlockCapture_event_file]
ON SERVER
STATE = STOP;

DROP EVENT SESSION [DeadlockCapture_event_file] ON SERVER;

Example 2: Deadlock Graph Capture with ring\_buffer Target

-- Ring buffer session: stores events in memory, wiped on server restart
-- Good for short-term capture without file I/O

CREATE EVENT SESSION [DeadlockCapture_ringbuffer]
ON SERVER
ADD EVENT sqlserver.xml_deadlock_report
(
    ACTION
    (
        sqlserver.sql_text,
        sqlserver.session_id,
        sqlserver.database_id,
        sqlserver.tsql_stack,
        package0.callstack
    )
)
ADD TARGET package0.ring_buffer
(
    SET max_memory = 4096
)
WITH (MAX_DISPATCH_LATENCY = 3 SECONDS);

ALTER EVENT SESSION [DeadlockCapture_ringbuffer] ON SERVER STATE = START;

-- Read the ring buffer (run this while the session is running)
SELECT target_data AS raw_xml
FROM sys.dm_xe_sessions s
JOIN sys.dm_xe_session_targets t ON s.address = t.event_session_address
WHERE s.name = 'DeadlockCapture_ringbuffer'
    AND t.target_name = 'ring_buffer';

-- Stop and drop when done
ALTER EVENT SESSION [DeadlockCapture_ringbuffer] ON SERVER STATE = STOP;
DROP EVENT SESSION [DeadlockCapture_ringbuffer] ON SERVER;

Example 3: Blocked Process Report

-- First: set the blocked process threshold (seconds)
EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;
EXEC sp_configure 'blocked process threshold', 30;
RECONFIGURE;

CREATE EVENT SESSION [BlockedProcessCapture]
ON SERVER
ADD EVENT sqlserver.blocked_process_report
(
    ACTION
    (
        sqlserver.sql_text,
        sqlserver.session_id,
        sqlserver.database_id,
        sqlserver.request_id,
        sqlserver.blocking_session_id,
        sqlserver.wait_type,
        sqlserver.wait_time,
        package0.callstack
    )
)
ADD TARGET package0.event_file
(
    SET filename = 'C:\XE\Blocks\BlockedProcesses.xel',
        max_file_size = 100,
        max_rollover_files = 5
)
WITH (MAX_DISPATCH_LATENCY = 10 SECONDS, STARTUP_STATE = OFF);

ALTER EVENT SESSION [BlockedProcessCapture] ON SERVER STATE = START;

-- Query blocked process report data — parses XML into readable form
SELECT
    xe.event_data.value('(//blocked-process/process[@identity[1]]/@spid)[1]', 'int') AS blocked_spid,
    xe.event_data.value('(//blocked-process/process[@identity[1]]/@inputbuf)[1]', 'nvarchar(max)') AS blocked_query,
    xe.event_data.value('(//blocked-process/blocking-process/process[@identity[1]]/@spid)[1]', 'int') AS blocking_spid,
    xe.event_data.value('(//blocked-process/blocking-process/process[@identity[1]]/@inputbuf)[1]', 'nvarchar(max)') AS blocking_query,
    xe.event_data.value('(//@waitTime)[1]', 'bigint') AS wait_time_ms,
    xe.event_data.value('(//@waitType)[1]', 'nvarchar(128)') AS wait_type,
    xe.timestamp
FROM (
    SELECT
        CONVERT(xml, event_data) AS event_data,
        timestamp
    FROM sys.fn_xe_file_target_read_file(
        'C:\XE\Blocks\BlockedProcesses*.xel',
        NULL, NULL, NULL
    )
    WHERE object_name = 'blocked_process_report'
) AS xe;

ALTER EVENT SESSION [BlockedProcessCapture] ON SERVER STATE = STOP;
DROP EVENT SESSION [BlockedProcessCapture] ON SERVER;

Example 4: Error Tracking with sqlserver.error\_reported

-- Track errors with severity >= 16 (user errors + above)
CREATE EVENT SESSION [ErrorTracking]
ON SERVER
ADD EVENT sqlserver.error_reported
(
    ACTION
    (
        sqlserver.sql_text,
        sqlserver.session_id,
        sqlserver.database_id,
        sqlserver.error_number,
        sqlserver.severity,
        sqlserver.state,
        sqlserver.message,
        package0.callstack
    )
    WHERE severity >= 16
)
ADD TARGET package0.ring_buffer
(
    SET max_memory = 16384
)
WITH (MAX_DISPATCH_LATENCY = 5 SECONDS, STARTUP_STATE = ON);

ALTER EVENT SESSION [ErrorTracking] ON SERVER STATE = START;

-- Query the error ring buffer
;WITH EventsCTE AS (
    SELECT
        e.event_data.value('(//data[@name="error_number"]/value)[1]', 'int') AS error_number,
        e.event_data.value('(//data[@name="message"]/value)[1]', 'nvarchar(max)') AS error_message,
        e.event_data.value('(//data[@name="severity"]/value)[1]', 'int') AS severity,
        e.event_data.value('(//data[@name="state"]/value)[1]', 'int') AS state,
        e.event_data.value('(//data[@name="session_id"]/value)[1]', 'int') AS session_id,
        e.event_data.value('(//data[@name="database_id"]/value)[1]', 'int') AS database_id,
        e.event_data.value('(//action[@name="sql_text"]/value)[1]', 'nvarchar(max)') AS sql_text,
        e.event_data.value('(//@timestamp)[1]', 'datetime') AS event_time
    FROM (
        SELECT
            CONVERT(xml, event_data) AS event_data,
            timestamp
        FROM sys.dm_xe_sessions s
        JOIN sys.dm_xe_session_targets t ON s.address = t.event_session_address
        WHERE s.name = 'ErrorTracking'
          AND t.target_name = 'ring_buffer'
    ) AS events(e)
)
SELECT
    error_number, error_message, severity, state,
    session_id, database_id, sql_text, event_time,
    COUNT(*) OVER (PARTITION BY error_number) AS error_count
FROM EventsCTE
ORDER BY event_time DESC;

-- For long-running error tracking, use event_file instead:
-- ADD TARGET package0.event_file (SET filename = 'C:\XE\Errors\Errors.xel', max_file_size = 100)

XEvents vs SQL Trace: The Comparison Table

Here is the direct comparison that matters for production DBA work:

| Capability | Extended Events | SQL Trace / Profiler |

| --- | --- | --- |

| Per-event overhead | Microseconds — designed for production use with minimal impact | High — legacy architecture, can significantly slow down a busy server |

| Maximum concurrent events | Unlimited — ring buffer and async targets handle high-volume capture | Limited — single trace can drop events under high load |

| Deadlock graph format | Clean XML with full call stacks, victims, resources — directly viewable in SSMS | Trace flags 1222/1204 dump text blobs to error log — format is hard to parse |

| Predicate filtering at source | Yes — events filtered before capture, reduces overhead to near zero | Limited — filter in Profiler UI but events already fired in engine |

| Target types | event\_file, ring\_buffer, histogram, pair\_matching, etw\_classic\_sync\_target, synchronous\_bucketizer | File only (.trc) |

| Configuration persistence | CREATE EVENT SESSION stored as T-SQL — version control, deploy via scripts, audit trail | Profiler GUI — difficult to reproduce, cannot script reliably, not source-control friendly |

| Session auto-start on server boot | Yes — STARTUP STATE = ON at session level | No — requires SQL Agent job or manual start after restart |

| System health session | Built-in system_health session — captures errors, deadlocks, memory dumps out of the box | No equivalent built-in |

| Query plan capture | Yes — sqlserver.query_plan_collected event with full XML plan | Yes — but adds significant overhead per captured event |

| Current status | Active development, recommended path forward | Deprecated since SQL Server 2012, no new features, removal planned |

Best Practices and Common Pitfalls

Do: Use Predicates to Filter at the Source

The biggest performance mistake in XEvents is capturing events that are too broad. Capturing sql_statement_completed on every statement, including sub-millisecond ad-hoc queries, will fill your target with noise and consume memory or disk space without providing useful signal. Use predicates: WHERE duration > 1000000 captures only statements running longer than 1 second (duration in microseconds).

Do: Use event\_file for Persistent Collections

Ring buffers are fine for short-term diagnostic sessions but are wiped on server restart and have a fixed buffer size that rolls over without warning. If you need to track something over days or weeks — deadlock frequency over a month, recurring errors after a deployment — use event_file. The files roll over, you can set retention policies, and you can query them months later.

Do: Start With the system\_health Session

SQL Server ships with a built-in system_health Extended Events session that runs by default on most installations. It captures errors of severity >= 20, deadlock graphs, and memory dumps. If you are in the middle of an incident and do not have a custom session running, query the system\_health ring buffer first — you may already have the data you need.

-- Check what system_health has captured
SELECT
    CAST(event_data AS xml) AS event_xml,
    timestamp
FROM sys.fn_xe_file_target_read_file(
    (SELECT f.value FROM sys.dm_xe_sessions s
     JOIN sys.dm_xe_session_targets t ON s.address = t.event_session_address
     JOIN sys.dm_xe_session_target_fields f ON t.target_name = 'event_file' AND f.name = 'filepath'
     WHERE s.name = 'system_health'),
    NULL, NULL, NULL
)
ORDER BY timestamp DESC;

Do Not: Point event\_file Targets at the C: Drive by Default

SQL Server service accounts typically do not have write access to arbitrary directories, and even if they do, filling the system drive with .xel files during a long-running session is a bad day. Create a dedicated directory on a non-system drive with appropriate permissions before you create a session with an event\_file target.

Do Not: Set MAX\_DISPATCH\_LATENCY to 0 in Production

Setting MAX_DISPATCH_LATENCY = 0 makes the session synchronous — events are captured in-line with the engine path. This eliminates latency but adds synchronous overhead on every captured event, which can create a feedback loop on a very high-volume session. Use MAX_DISPATCH_LATENCY = 1 SECONDS or higher for production sessions.

Do Not: Forget to Stop Long-Running Sessions

An event_file session running with max_file_size = 50 and max_rollover_files = 10 will use up to 500 MB of disk space before it rolls over. Build session management into your runbook: create, start, capture, stop, drop.

Need help setting up XEvent sessions for a specific workload?

Extended Events sessions are specific to what you are trying to diagnose. If you have a recurring performance issue, a blocking problem, or a deadlock pattern that Profiler is not capturing cleanly, get a free environment review. Bring your production wait stats, your blocking chain history, and whatever error messages have been appearing — we will work out which session configuration actually answers your question.

Further Reading

• → SQL Server Transactional Replication: A Deep Dive
• → SQL Server Always On AG: The Complete Setup Guide
• → SQL Server Performance Monitoring with DMVs and Extended Events
• → Free assessment: Get your XE diagnostic sessions reviewed by a senior DBA

Free · Takes 2 Minutes

Get your free database health score

Find out exactly where your database is vulnerable before it causes an incident. 30+ years of DBA expertise, distilled into a single assessment.

Get my free health score

Download the DBA checklist

##