Study guide

8.4 Monitoring with Extended Events

DP-300: Administering Microsoft Azure SQL Solutions Exam

Track your progress as you read

Finished sections get a check mark.

1 Introduction & Exam Overview

1.1 Current DP-300 Exam Facts 1.2 Skills Outline and Domain Weights 1.3 Study Strategy and Official Resources

2 Planning and Deploying Azure SQL Solutions

2.1 Choosing and Deploying Azure SQL Database Offerings 2.2 Automated Deployment, Azure Arc, and SQL Database in Microsoft Fabric 2.3 Table Partitioning and Sharding Strategy 2.4 Hybrid SQL Server and IaaS Patching

3 Configuring Resources for Scale and Performance

3.1 Configuring Azure SQL Database for Scale and Performance 3.2 Configuring Azure SQL Managed Instance for Scale and Performance 3.3 Configuring SQL Server on Azure VMs for Scale and Performance 3.4 Configuring Table Partitioning and Data Compression

4 Planning and Implementing a Migration Strategy

4.1 Evaluating Migration Requirements and Strategies 4.2 Implementing Online, Offline, and Azure Migrations 4.3 Migrating Between Azure SQL Services and Troubleshooting

5 Configuring Database Authentication and Authorization

5.1 Microsoft Entra ID Authentication for Azure SQL 5.2 Authentication for SQL on Azure VMs and Managed Instance; Troubleshooting 5.3 Security Principals and Entra-Backed Users 5.4 Permissions, Least Privilege, and T-SQL Management

6 Implementing Security for Data at Rest and in Transit

6.1 Transparent Data Encryption and Object-Level Encryption 6.2 Always Encrypted and VBS Enclaves 6.3 Firewall Rules, Private Links, and Service Endpoints

7 Implementing Compliance Controls for Sensitive Data

7.1 Data Classification and Server/Database Auditing 7.2 Change Data Tracking and Dynamic Data Masking 7.3 Row-Level Security and Ledger in Azure SQL

8 Monitoring Resource Activity and Performance

8.1 Operational Baselines and Performance Metric Sources 8.2 Interpreting Metrics and Configuring Activity Monitoring 8.3 Monitoring with Database Watcher (Preview) 8.4 Monitoring with Extended Events

9 Monitoring and Optimizing Query Performance

9.1 Configuring and Monitoring with Query Store 9.2 Session Blocking and Dynamic Management Views 9.3 Index Changes and Execution Plan Review 9.4 Query Construct Modifications and Intelligent Insights

10 Configuring Database Solutions for Optimal Performance

10.1 Index, Statistics, and Integrity Maintenance 10.2 Automatic Tuning and Database-Scoped Configuration 10.3 Server Settings and Resource Governor 10.4 Compute/Storage Scaling and Intelligent Query Processing

11 Creating and Managing SQL Server Agent Jobs

11.1 Schedules for Regular Maintenance Jobs 11.2 Job Alerts, Notifications, and Agent Troubleshooting 11.3 Automating Deployment with ARM and Bicep Templates

12 Automating Deployments and Database Tasks in Azure

12.1 Azure PowerShell, Azure CLI, and Deployment Monitoring 12.2 Elastic Jobs for Cross-Database Automation 12.3 Azure Automation Runbooks, Alerts, and Troubleshooting Automated Tasks

13 Planning an HA/DR Strategy and Backup and Restore

13.1 Recommending HA/DR Strategy from RPO and RTO 13.2 Hybrid and Azure-Specific HA/DR Solutions and Testing 13.3 Backup and Restore Strategy with Native Tools 13.4 Point-in-Time Restore and Recovery Operations

14 Configuring HA/DR: Long-Term Retention, Geo-Replication, and Failover Groups

14.1 Long-Term Retention, T-SQL Backup/Restore, and Cloud Storage 14.2 Active Geo-Replication 14.3 Failover Groups

15 Advanced HA/DR: Availability Groups, FCIs, Log Shipping, and Troubleshooting

15.1 Always On Availability Groups on SQL Managed Instance and Azure VMs 15.2 Always On Failover Cluster Instances and Log Shipping 15.3 Monitoring and Troubleshooting HA/DR Solutions

8.4 Monitoring with Extended Events

Key Takeaways Explain This Topic AI

Extended Events (XEvents) is the lightweight, highly scalable successor to SQL Trace and SQL Profiler, which are deprecated and removed from Azure SQL Database; XEvents is the supported tracing mechanism on every Azure SQL target

An XEvent session binds events (what happens) from packages (sqlserver, sqlos, package0) with actions (extra columns to capture) and predicates (filters), and writes to targets such as event_file, ring_buffer, histogram, pair_matching, or bucketizer

The system_health session is a default, always-on XEvent session on SQL Server and SQL MI that captures severe errors, memory pressure, and other critical events - inspect it first when triaging an unexplained incident

Use event_file with rollover (max_rollover_files) for durable capture of blocking, long queries, and deadlocks; read .xel files back with sys.fn_xe_file_target_read_file, not by opening the file directly

On Azure SQL Database you can create and run XEvent sessions but cannot start/stop them with the GUI; T-SQL is required, and the session metadata differs slightly from SQL Server and SQL MI - verify the available events and targets against the target environment

Last updated: August 2026

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:

Target Behavior Use case

event_file Writes events to .xel files on disk (or Azure Storage blob on Azure SQL DB), supports rollover Durable capture of long-running traces; post-hoc analysis

ring_buffer Keeps events in memory, FIFO; lost when the session stops Quick, short investigations; not for large volumes

histogram Counts events by a specified column value Frequency analysis: top queries, top wait types

pair_matching Matches begin/end events and reports unmatched ones Orphaned events: e.g., statements that started but never completed

bucketizer Groups events into buckets by a column Aggregation analysis, similar to histogram with different semantics

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

  • Target XEvents available Session scope event_file destination
  • SQL Server on Azure VMs Full event catalog ON SERVER Local disk or UNC

SQL Managed Instance Near-full catalog (instance-scoped and some DB-scoped) ON SERVER primarily Azure Storage blob

Azure SQL Database Subset of events; no server-scoped catalog ON DATABASE Azure Storage blob only (SAS URL)

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.