Tested on SQL Server 2022 (Developer edition, single test instance), October 2026.

Extended Events (XE) is the lightweight replacement for SQL Trace and SQL Profiler, which Microsoft has deprecated. Sessions are defined in T-SQL, filtered at source and written to a file, so they can run on busy production servers with little overhead.

The system_health session

SQL Server runs a session called system_health all the time. It records deadlocks, severe errors, long latch and lock waits and memory problems, so it is the first place to look after an incident.

-- Deadlocks recorded by system_health, newest first
SELECT xed.value('@timestamp', 'datetime2') AS event_time_utc,
       xed.query('.') AS deadlock_graph
FROM (SELECT CAST(event_data AS xml) AS event_xml
      FROM sys.fn_xe_file_target_read_file(N'system_health*.xel', NULL, NULL, NULL)
      WHERE object_name = N'xml_deadlock_report') AS t
CROSS APPLY t.event_xml.nodes('/event') AS x(xed)
ORDER BY event_time_utc DESC;

Click the deadlock_graph value in SSMS, save it as a .xdl file and reopen it to see the graph. A new deadlock can take a minute or two to appear, because system_health writes to its file in batches.

Step 1: Create a session for long-running queries

CREATE EVENT SESSION [LongQueries] ON SERVER
ADD EVENT sqlserver.sql_statement_completed (
    ACTION (sqlserver.client_app_name, sqlserver.client_hostname, sqlserver.database_name,
            sqlserver.session_id, sqlserver.sql_text, sqlserver.username)
    WHERE duration > 5000000),          -- microseconds: 5 seconds
ADD EVENT sqlserver.rpc_completed (
    ACTION (sqlserver.client_app_name, sqlserver.client_hostname, sqlserver.database_name,
            sqlserver.session_id, sqlserver.sql_text, sqlserver.username)
    WHERE duration > 5000000)
ADD TARGET package0.event_file (SET filename = N'LongQueries.xel',
                                    max_file_size = 100, max_rollover_files = 5)
WITH (MAX_DISPATCH_LATENCY = 5 SECONDS, STARTUP_STATE = OFF);

A file name without a path is written to the instance’s LOG folder. max_file_size is in MB, so this session uses at most 500 MB.

Step 2: Start and stop it

-- Start collecting
ALTER EVENT SESSION [LongQueries] ON SERVER STATE = START;
-- Stop when you have enough (run this separately, later)
ALTER EVENT SESSION [LongQueries] ON SERVER STATE = STOP;

-- Sessions currently running
SELECT name, create_time FROM sys.dm_xe_sessions;

Step 3: Read the results

SELECT x.value('(event/@timestamp)[1]', 'datetime2') AS event_time_utc,
       x.value('(event/@name)[1]', 'sysname') AS event_name,
       x.value('(event/data[@name="duration"]/value)[1]', 'bigint') / 1000 AS duration_ms,
       x.value('(event/data[@name="cpu_time"]/value)[1]', 'bigint') / 1000 AS cpu_ms,
       x.value('(event/data[@name="logical_reads"]/value)[1]', 'bigint') AS logical_reads,
       x.value('(event/action[@name="database_name"]/value)[1]', 'sysname') AS database_name,
       x.value('(event/action[@name="client_app_name"]/value)[1]', 'nvarchar(256)') AS app_name,
       x.value('(event/action[@name="sql_text"]/value)[1]', 'nvarchar(max)') AS sql_text
FROM (SELECT CAST(event_data AS xml) AS x
      FROM sys.fn_xe_file_target_read_file(N'LongQueries*.xel', NULL, NULL, NULL)) AS t
ORDER BY duration_ms DESC;

In SSMS you can also open the .xel file directly, or right-click the session and choose Watch Live Data.

A session for blocking

The blocked process report fires whenever a task has been blocked for longer than a threshold you set.

EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;
EXEC sp_configure 'blocked process threshold (s)', 20;
RECONFIGURE;

CREATE EVENT SESSION [BlockedProcesses] ON SERVER
ADD EVENT sqlserver.blocked_process_report
ADD TARGET package0.event_file (SET filename = N'BlockedProcesses.xel',
                                    max_file_size = 50, max_rollover_files = 4)
WITH (STARTUP_STATE = ON);

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

Good practice

• Filter in the WHERE clause of the event, not afterwards. Unfiltered statement-level events on a busy server produce huge files.
• Use the event_file target for anything you will keep. The ring_buffer target is held in memory and is easy to lose.
• Remove sessions you no longer need: DROP EVENT SESSION [LongQueries] ON SERVER;
• Profiler traces translate roughly as: SQL:BatchCompleted to sql_batch_completed, RPC:Completed to rpc_completed, SQL:StmtCompleted to sql_statement_completed.

From my SQL Server DBA handbook.