Introduction to SQL Server Extended Events: replace Profiler traces with lightweight sessions you can leave running

#Why Extended Events

SQL Server Profiler and server-side traces are deprecated and heavy. Extended Events (XE) are the replacement: lower overhead, filterable at the source, and able to capture things Profiler can't (wait stats per query, deadlock graphs, memory grants).

Terms you need:

#Step 1: Use what's already running: system_health

Every instance has a system_health session running. It captures deadlocks, severe errors, long latch/lock waits and more. Read its deadlock reports:

SELECT  CAST(event_data AS XML).value('(event/@timestamp)[1]', 'datetime2') AS utc_time,
        CAST(event_data AS XML).query('(event/data/value/deadlock)[1]')   AS deadlock_graph
FROM    sys.fn_xe_file_target_read_file('system_health*.xel', NULL, NULL, NULL)
WHERE   object_name = 'xml_deadlock_report'
ORDER BY utc_time DESC;

Click a deadlock_graph value in SSMS, save it as .xdl, and reopen it to see the graphical deadlock view.

Tip

system_health files roll over, so history is limited (often a few days on busy servers). Copy deadlock graphs out during an incident before they age away.

#Step 2: Create a session for slow queries

Capture statements longer than one second, with the text and who ran them:

CREATE EVENT SESSION [slow_queries] ON SERVER
ADD EVENT sqlserver.sql_statement_completed (
    ACTION (sqlserver.sql_text, sqlserver.database_name, sqlserver.client_app_name,
            sqlserver.client_hostname, sqlserver.username, sqlserver.query_hash)
    WHERE  duration > 1000000             -- microseconds = 1 s
      AND  sqlserver.is_system = 0
),
ADD EVENT sqlserver.rpc_completed (
    ACTION (sqlserver.sql_text, sqlserver.database_name, sqlserver.client_app_name,
            sqlserver.client_hostname, sqlserver.username, sqlserver.query_hash)
    WHERE  duration > 1000000
)
ADD TARGET package0.event_file (
    SET filename = N'D:\XE\slow_queries.xel', max_file_size = 100, max_rollover_files = 10
)
WITH (MAX_DISPATCH_LATENCY = 30 SECONDS, STARTUP_STATE = OFF, EVENT_RETENTION_MODE = ALLOW_SINGLE_EVENT_LOSS);

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

• rpc_completed catches stored procedure and parameterised calls from applications; sql_statement_completed catches ad hoc batches' statements.
• The folder (D:\XE\) must exist and the SQL Server service account must be able to write to it.
• ALLOW_SINGLE_EVENT_LOSS keeps overhead low on busy systems.

#Step 3: Read the results with T-SQL

;WITH x AS (
    SELECT CAST(event_data AS XML) AS ed
    FROM   sys.fn_xe_file_target_read_file(N'D:\XE\slow_queries*.xel', NULL, NULL, NULL)
)
SELECT TOP (50)
       ed.value('(event/@timestamp)[1]', 'datetime2')                                AS utc_time,
       ed.value('(event/@name)[1]', 'varchar(50)')                                   AS event_name,
       ed.value('(event/data[@name="duration"]/value)[1]', 'bigint') / 1000          AS duration_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,
       ed.value('(event/action[@name="sql_text"]/value)[1]', 'nvarchar(max)')        AS sql_text
FROM   x
ORDER  BY duration_ms DESC;

You can also open the .xel file in SSMS (File > Open), or right-click the session > Watch Live Data for a live view with grouping and filtering.

#Step 4: Capture errors applications swallow

Apps often hide database errors. This session records errors of severity 11+ with the statement that caused them:

CREATE EVENT SESSION [app_errors] ON SERVER
ADD EVENT sqlserver.error_reported (
    ACTION (sqlserver.sql_text, sqlserver.database_name, sqlserver.client_app_name, sqlserver.username)
    WHERE  severity >= 11
      AND  error_number <> 5701 AND error_number <> 5703   -- "changed database context" noise
)
ADD TARGET package0.ring_buffer (SET max_memory = 10240)   -- KB
WITH (STARTUP_STATE = OFF);

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

Read the ring buffer:

SELECT  e.value('(@timestamp)[1]', 'datetime2')                          AS utc_time,
        e.value('(data[@name="error_number"]/value)[1]', 'int')          AS error_number,
        e.value('(data[@name="message"]/value)[1]', 'nvarchar(2048)')   AS message,
        e.value('(action[@name="sql_text"]/value)[1]', 'nvarchar(max)') AS sql_text
FROM   (SELECT CAST(t.target_data AS XML) AS td
        FROM   sys.dm_xe_session_targets t
        JOIN   sys.dm_xe_sessions s ON s.address = t.event_session_address
        WHERE  s.name = 'app_errors' AND t.target_name = 'ring_buffer') AS src
CROSS APPLY td.nodes('RingBufferTarget/event') AS n(e)
ORDER BY utc_time DESC;

Ring buffer is in memory and lost on restart or session stop; use event_file for anything you need to keep.

#Step 5: Manage sessions

-- What sessions exist and which are running
SELECT s.name, CASE WHEN r.name IS NULL THEN 'stopped' ELSE 'running' END AS state
FROM   sys.server_event_sessions s
LEFT JOIN sys.dm_xe_sessions r ON r.name = s.name;

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

Find event and column names when building your own:

SELECT p.name AS package, o.name AS event, o.description
FROM   sys.dm_xe_objects o JOIN sys.dm_xe_packages p ON p.guid = o.package_guid
WHERE  o.object_type = 'event' AND o.name LIKE '%wait%';

#Overhead: keep it safe

Warning

Avoid unfiltered sessions on high-volume events (sql_statement_completed with no duration filter, lock_acquired, wait_info on all waits). They can generate gigabytes per minute and hurt performance. Always filter at the predicate, prefer event_file, and stop sessions you no longer need.

Also avoid the query_post_execution_showplan event on production except for short, tightly filtered captures; it's expensive.

#Azure SQL Database note

Azure SQL Database uses ON DATABASE sessions and writes event_file targets to Azure Blob Storage with a credential, not local disk. The event names and query patterns are otherwise similar.

#Prevent problems later

• Keep a small library of tested session scripts (slow queries, errors, deadlocks, blocking) in source control.
• Prefer Query Store for "which queries got slower"; use XE for who/when/what-error details Query Store doesn't have.
• Alert on xml_deadlock_report counts instead of finding out from users.
• Clean up .xel files and stopped sessions after investigations.