Extended Events Sessions, Targets, Event Fields, Actions, Causality, and Low-Overhead Tracing

Learning outcomes

The ServiceHub slowdown lasts only a few seconds and disappears before anyone can query the DMVs. Polling faster is one answer, but it can add overhead and still miss the exact transition. Extended Events (XEvents/XE) is SQL Server’s event-oriented diagnostic framework: you choose specific engine events, optional fields/actions, predicates, buffering and targets so that evidence is captured when the event occurs.

1. XE records occurrences, not periodic snapshots

An event is something that happened, such as a statement completing or an error being reported. Event fields are intrinsic to that event. An action is additional context—SQL text, database name, session id, client application—that XE can attach when the event fires. A predicate filters before data reaches the target, which is one of the most important overhead controls. A target receives event output. The event session owns the configuration and buffers.

The lab dynamically embeds the current @@SPID as a predicate. That is safer than capturing every statement on the instance and filtering later.

USE master;GOIF EXISTS (SELECT 1 FROM sys.server_event_sessions WHERE name = N'ServiceHub_Ch22_Focused')    DROP EVENT SESSION ServiceHub_Ch22_Focused ON SERVER;GODECLARE @sid int = @@SPID;DECLARE @sql nvarchar(max) = N'CREATE EVENT SESSION ServiceHub_Ch22_Focused ON SERVERADD EVENT sqlserver.sql_statement_completed(    ACTION    (        sqlserver.database_name,        sqlserver.client_app_name,        sqlserver.session_id,        sqlserver.sql_text    )    WHERE ([sqlserver].[session_id] = (' + CONVERT(nvarchar(12), @sid) + N'))),ADD EVENT sqlserver.error_reported(    ACTION    (        sqlserver.database_name,        sqlserver.client_app_name,        sqlserver.session_id,        sqlserver.sql_text    )    WHERE ([sqlserver].[session_id] = (' + CONVERT(nvarchar(12), @sid) + N')))ADD TARGET package0.ring_buffer(    SET max_memory = 1024)WITH(    MAX_MEMORY = 4 MB,    EVENT_RETENTION_MODE = ALLOW_SINGLE_EVENT_LOSS,    MAX_DISPATCH_LATENCY = 5 SECONDS,    TRACK_CAUSALITY = ON,    STARTUP_STATE = OFF);';EXEC sys.sp_executesql @sql;ALTER EVENT SESSION ServiceHub_Ch22_Focused ON SERVER STATE = START;GO

TRACK_CAUSALITY = ON adds activity identifiers that can help relate events in a causally connected sequence. It does not magically correlate SQL Server to every external service; cross-system correlation still needs application/request identifiers and synchronized time.

2. Generate events and read the ring buffer

Run the following in the same query window that created the session so the session-id predicate matches. The divide-by-zero is intentional and harmless; it demonstrates that successful statements and errors can coexist in one incident stream.

USE ServiceHubObservabilityLab;GOSELECT COUNT_BIG(*) AS open_ordersFROM lab22.WorkOrderWHERE status = 'OPEN';GOBEGIN TRY    SELECT 1 / 0 AS deliberate_error;END TRYBEGIN CATCH    SELECT ERROR_NUMBER() AS error_number, ERROR_MESSAGE() AS error_message;END CATCH;GOSELECT TOP (10) work_order_id, customer_id, priorityFROM lab22.WorkOrderORDER BY opened_at DESC;GO

;WITH TargetData AS(    SELECT CAST(t.target_data AS xml) AS target_xml    FROM sys.dm_xe_session_targets AS t    JOIN sys.dm_xe_sessions AS s      ON s.address = t.event_session_address    WHERE s.name = N'ServiceHub_Ch22_Focused'      AND t.target_name = N'ring_buffer'), Events AS(    SELECT n.query('.') AS event_xml    FROM TargetData    CROSS APPLY target_xml.nodes('/RingBufferTarget/event') AS q(n))SELECT    event_xml.value('(event/@timestamp)[1]', 'datetime2(7)') AS event_time_utc,    event_xml.value('(event/@name)[1]', 'sysname') AS event_name,    event_xml.value('(event/action[@name="database_name"]/value)[1]', 'sysname') AS database_name,    event_xml.value('(event/action[@name="session_id"]/value)[1]', 'int') AS session_id,    event_xml.value('(event/action[@name="sql_text"]/value)[1]', 'nvarchar(max)') AS sql_textFROM EventsORDER BY event_time_utc;

The ring buffer is memory-only. Stopping the session discards its output, older events are overwritten when capacity is reached, and converting target data to XML has a 4-MB document limit. Microsoft recommends keeping ring-buffer memory bounded; the lab uses 1 MB. This makes it useful for a short local exercise, not a durable incident archive.

3. event_file is the normal durable target for boxed SQL Server

The event_file target writes binary .xel files. The SQL Server service account needs filesystem access, the path differs between Windows/Linux/container deployments, and the telemetry can contain sensitive SQL text or parameters. That is why the lab does not hard-code a fictitious writable path. Discover an approved directory, grant the minimum OS access, set rollover/size limits, and protect/expire the files according to policy.

-- Stop and recreate a disposable session if you want a durable target.-- Replace the filename below with a path writable by the SQL Server service account.-- Windows example: C:\SqlXEvents\ServiceHub_Ch22.xel-- Linux example:   /var/opt/mssql/log/ServiceHub_Ch22.xelCREATE EVENT SESSION ServiceHub_Ch22_FileExample ON SERVERADD EVENT sqlserver.error_reported(    ACTION(sqlserver.database_name, sqlserver.session_id, sqlserver.sql_text))ADD TARGET package0.event_file(    SET filename = N'<APPROVED_PATH>/ServiceHub_Ch22.xel',        max_file_size = 20,        max_rollover_files = 4)WITH (STARTUP_STATE = OFF);-- Read after substituting the real wildcard path:-- SELECT * FROM sys.fn_xe_file_target_read_file(N'<APPROVED_PATH>/ServiceHub_Ch22*.xel', NULL, NULL, NULL);

The placeholder is deliberately not runnable until you replace it. That is safer than teaching a path that fails on half the supported platforms or encouraging learners to make an arbitrary directory world-writable.

4. Overhead comes from what you collect, not from the XE brand name

Extended Events is designed to be lightweight, but a badly scoped session can still be expensive. Capturing high-frequency events, collecting query text/call stacks, using synchronous targets, writing to slow storage, or omitting predicates can perturb the very workload you are measuring. “XE is low overhead” means the framework enables low-overhead designs; it does not exempt the collector from measurement.

SQL Trace and SQL Server Profiler are deprecated for new diagnostic design. XE offers richer engine integration and filtering. Profiler remains useful for understanding older estates, but a new incident runbook should normally script the XE session so scope, retention and permissions are reviewable.

5. Verify, stop, and remove the disposable session

SELECT    ses.name,    CASE WHEN xs.name IS NULL THEN 0 ELSE 1 END AS is_running,    ses.startup_state,    ses.event_retention_mode_desc,    ses.max_memory,    ses.max_dispatch_latencyFROM sys.server_event_sessions AS sesLEFT JOIN sys.dm_xe_sessions AS xs  ON xs.name = ses.nameWHERE ses.name = N'ServiceHub_Ch22_Focused';GOALTER EVENT SESSION ServiceHub_Ch22_Focused ON SERVER STATE = STOP;GO-- Keep the definition for Lesson 5 if you want to restart it.-- Final chapter cleanup drops the session.

For production, write down the hypothesis, event names, actions, predicates, target, retention, expected event rate, sensitive fields, start/stop criteria, owner and rollback. Validate overhead on representative traffic. A session that no one remembers to stop is an operational bug.

Authoritative references

• Extended Events overview
• Quickstart: Extended Events
• Extended Events targets
• View event data in SSMS
• Use the system_health session