8.4 Monitoring with Extended Events

XEvents vs SQL Trace and Profiler

Extended Events (XEvents) is the modern tracing and diagnostics framework for the SQL Server engine and its Azure SQL descendants. SQL Trace and its GUI front-end SQL Profiler are the older equivalents and are deprecated; SQL Profiler has been removed from SQL Server Management Studio's default install, and SQL Trace does not exist on Azure SQL Database at all. On every Azure SQL target - SQL Server on Azure VMs, SQL Managed Instance, and Azure SQL Database - Extended Events is the supported, performant tracing mechanism. The exam almost always asks you to choose XEvents over Profiler when a tracing scenario is presented, and to know that Profiler is not available on Azure SQL Database.

The performance argument is real: XEvents are designed to be lightweight, run in the engine's own memory, and avoid the heavy overhead that SQL Trace imposed on busy systems. Running a Profiler trace against a production server was historically a risk; running the equivalent XEvent session is the default recommendation even for high-throughput systems, as long as you scope predicates and use file targets rather than ring buffers for large captures.

Packages, Events, Actions, Predicates, Targets

The XEvent object model has five concepts to remember:

• Packages are containers of objects. The three packages you encounter are package0 (infrastructure primitives), sqlos (scheduler and OS-related events), and sqlserver (the bulk of engine events: sqlserver.sql_statement_completed, sqlserver.sp_statement_completed, sqlserver.error_reported, sqlserver.lock_deadlock, etc.).
• Events are things that happen: statement completed, error reported, deadlock, blocked process report. Each event has a fixed set of columns plus customizable data.
• Actions are extra columns the engine attaches when an event fires - for example, sqlserver.sql_text (capture the T-SQL), sqlserver.database_name, sqlserver.client_app_name, sqlserver.client_hostname, package0.collect_system_time.
• Predicates are filters that decide whether an event is collected. Predicates can short-circuit early (for example, only fire sql_statement_completed when duration > 5000000 microseconds, i.e., 5 seconds) to keep the target small.
• Targets are where events are written. The five targets worth knowing:

A common exam trap: use event_file, not ring_buffer, for any capture you intend to keep or analyze after the session stops. Ring buffer contents are lost when the session stops or memory pressure forces eviction.

Creating and Managing Sessions

Sessions are managed with T-SQL: CREATE EVENT SESSION, ALTER EVENT SESSION ... ADD EVENT ..., ALTER EVENT SESSION ... ADD TARGET ..., ALTER EVENT SESSION ... STATE = START / STATE = STOP to start and stop it, and DROP EVENT SESSION to remove it. On SQL Server and SQL MI the SSMS GUI also provides a New Session wizard, but on Azure SQL Database the GUI is not available and T-SQL is required.

Example: capture statements running longer than 5 seconds.

CREATE EVENT SESSION [LongQueries] ON DATABASE
ADD EVENT sqlserver.sql_statement_completed
(
    ACTION(
        sqlserver.sql_text,
        sqlserver.database_name,
        sqlserver.client_app_name,
        sqlserver.client_hostname,
        package0.collect_system_time
    )
    WHERE duration > 5000000  -- microseconds, so 5 seconds
)
ADD TARGET package0.event_file
(
    SET filename = N'https://mystorage.blob.core.windows.net/xevents/LongQueries.xel',
        max_file_size = 50,    -- MB
        max_rollover_files = 5 -- keep 5 files, older ones drop
)
WITH
(
    MAX_MEMORY = 4096 KB,
    EVENT_RETENTION_MODE = ALLOW_SINGLE_EVENT_LOSS,
    MAX_DISPATCH_LATENCY = 5 SECONDS,
    STARTUP_STATE = ON
);

ALTER EVENT SESSION [LongQueries] ON DATABASE STATE = START;

A few specifics from that example that the exam probes:

• On Azure SQL Database, CREATE EVENT SESSION ... ON DATABASE (database-scoped). On SQL Server and SQL MI, ON SERVER (server-scoped).
• The event_file target on Azure SQL Database writes to an Azure Storage blob URL, not a local file path; you must create a SAS token for the container and put it in the URL.
• max_rollover_files keeps a bounded set of files; older ones are deleted, which prevents disk or blob consumption from growing unbounded.
• EVENT_RETENTION_MODE = ALLOW_SINGLE_EVENT_LOSS trades the rare dropped event for lower overhead; NO_EVENT_LOSS blocks the workload to preserve every event and is almost never the right choice in production.
• MAX_MEMORY caps the buffer memory for the session; when full, the engine either drops events or blocks, depending on the retention mode.

To stop and remove:

ALTER EVENT SESSION [LongQueries] ON DATABASE STATE = STOP;
DROP EVENT SESSION [LongQueries] ON DATABASE;

The system_health Session

SQL Server and SQL Managed Instance ship with a default, always-on XEvent session called system_health. It captures critical events with negligible overhead, including severe errors (severity >= 20), memory pressure (including out-of-memory), non-yielding schedulers, and some blocking and wait-info events. The system_health ring buffer is the first place to look when triaging an unexplained incident on a SQL Server or SQL MI instance; you can query it directly:

SELECT CAST(target_data AS xml) AS system_health_xml
FROM sys.dm_xe_session_targets t
JOIN sys.dm_xe_sessions s ON s.address = t.event_session_address
WHERE s.name = 'system_health';

On Azure SQL Database, system_health is not exposed the same way; you create your own session for similar coverage.

Capturing Blocking, Long Queries, and Deadlocks

Three canonical XEvent use cases:

• Blocking: use the sqlserver.blocked_process_report event, which fires when the blocked process threshold is exceeded. Configure the threshold with sp_configure 'blocked process threshold', 5 (seconds) on SQL Server / SQL MI; on Azure SQL DB set it via database-scoped configuration. The event report includes the blocked and the blocker SPIDs and resources.
• Long queries: as in the example above, sqlserver.sql_statement_completed with a duration predicate. Tune the predicate to keep the target small.
• Deadlocks: the sqlserver.xml_deadlock_report event produces the full deadlock graph; on SQL Server and SQL MI the system_health session already captures this, but a dedicated session with an event_file target gives you durable, rotation-bounded history for trend analysis.

Reading .xel Files

Event file targets are not read by opening the file in a text editor; the .xel format is binary. Use the table-valued function sys.fn_xe_file_target_read_file to read events into a relational result set:

SELECT CAST(event_data AS xml) AS event_xml
FROM sys.fn_xe_file_target_read_file(
    'https://mystorage.blob.core.windows.net/xevents/LongQueries*.xel',
    NULL, NULL, NULL);

On SQL Server, the path is a local or UNC file path. On Azure SQL Database, it is the blob URL (with the SAS token embedded if the container is private). The function returns one row per event, with event_data as XML that you can shred with nodes() and value() to get the columns and actions. SSMS also has a GUI viewer that opens .xel files directly when connected to an instance.

Differences Across Azure SQL Targets

The exam tests that you know: SQL Profiler/SQL Trace is not available on Azure SQL Database, XEvent sessions on Azure SQL DB are database-scoped and write to blob storage, and the GUI session wizard is not available for Azure SQL DB - T-SQL is the supported authoring path. The event catalog is also smaller on Azure SQL DB, so before designing a session, verify that the specific event (for example, blocked_process_report) is available on your target.