Building Reusable Extended Events Sessions in SQL Server for Ongoing Monitoring

The one-off XEvents session from the 13-Hour Delete case study was diagnostic and temporary. A production monitoring session needs to run continuously with minimal overhead — different design goals entirely.

Designing for Low Overhead

CREATE EVENT SESSION LongRunningQueries ON SERVER
ADD EVENT sqlserver.sql_statement_completed (
    ACTION (sqlserver.sql_text, sqlserver.username, sqlserver.client_hostname)
    WHERE duration > 5000000  -- 5 seconds, in microseconds — filter aggressively
)
ADD TARGET package0.event_file (
    SET filename = N'LongRunningQueries', max_file_size = 50, max_rollover_files = 5
)
WITH (MAX_MEMORY = 4096 KB, EVENT_RETENTION_MODE = ALLOW_SINGLE_EVENT_LOSS, MAX_DISPATCH_LATENCY = 5 SECONDS);
GO
ALTER EVENT SESSION LongRunningQueries ON SERVER STATE = START;

Three choices make this production-safe rather than a diagnostic one-off: an aggressive WHERE duration > filter (only capture what actually matters), a rollover file target with a size cap (bounded disk usage), and ALLOW_SINGLE_EVENT_LOSS (never let monitoring itself become a bottleneck).

Auto-Starting on Server Restart

ALTER EVENT SESSION LongRunningQueries ON SERVER WITH (STARTUP_STATE = ON);

Reading the Results Later

SELECT event_data.value('(event/@timestamp)[1]', 'DATETIME2') AS event_time,
    event_data.value('(event/data[@name="duration"]/value)[1]', 'BIGINT') / 1000000.0 AS duration_sec,
    event_data.value('(event/action[@name="sql_text"]/value)[1]', 'NVARCHAR(MAX)') AS sql_text
FROM sys.fn_xe_file_target_read_file('LongRunningQueries*.xel', NULL, NULL, NULL)
CROSS APPLY (SELECT CAST(event_data AS XML) AS event_data) ed
ORDER BY event_time DESC;