Extended Events (XEvents) in SQL Server & Azure SQL: Complete Guide

Every DBA eventually has this experience: a support ticket comes in saying “the app is slow,” with zero other detail, and it’s your job to figure out which of the thousand things happening inside SQL Server is actually the culprit. Was it blocking? A bad plan? A login storm? Someone’s ad-hoc query that forgot a WHERE clause and is currently scanning eleven million rows?

For a long time, the answer to “let’s watch what’s happening in real time” was SQL Server Profiler, sitting on top of SQL Trace. It worked, but it worked the way a floodlight works when what you actually needed was a flashlight — it captured everything indiscriminately, ran client-side, and could meaningfully slow down a busy production server just by being turned on. Enough DBAs have a story about a well-intentioned Profiler trace making an already-struggling server worse that Microsoft eventually stopped recommending it entirely.

Extended Events (XEvents) is what replaced it, and it’s not a minor upgrade — it’s a fundamentally different architecture, and it’s the tool DP-300 expects you to know cold. This article covers what it is, why it beats the old tools, and — the part that actually matters day to day — how to use it to catch blocking, deadlocks, slow queries, timeouts, high CPU, login failures, and wait statistics, across SQL Server, Azure SQL Database, and Azure SQL Managed Instance.

What Extended Events Actually Are

Extended Events is a lightweight event-handling system built directly into the SQL Server engine (and its Azure siblings). Instead of a separate trace mechanism bolted on the side, XEvents taps directly into engine components and lets you subscribe to specific things happening inside — a query starting, a lock being taken, a deadlock being detected, a login failing — with almost none of the overhead that made SQL Trace risky to run in production.

The architecture is built from a small number of building blocks that, once they click, make the whole system feel much less mysterious:

┌──────────────────────────────────────────────────────────────┐
│                         EVENT SESSION                          │
│                                                                  │
│   EVENTS                 ACTIONS              PREDICATES        │
│   "what happened"    "extra context to     "only capture       │
│   e.g. sql_statement_    attach"             events matching    │
│   completed,          e.g. sql_text,          this condition"   │
│   deadlock_graph,      client_hostname,       e.g. duration >   │
│   login_failed         session_id             5000ms            │
│        │                    │                       │            │
│        └────────────────────┴───────────────────────┘            │
│                              │                                   │
│                              ▼                                   │
│                          TARGET                                  │
│              where the filtered, enriched events go              │
│      ring_buffer (in-memory) │ event_file (durable, .xel)         │
│      histogram │ event_counter │ event_stream (live)              │
└──────────────────────────────────────────────────────────────┘

• Events — the “thing that happened” you want to know about (a query finishing, a deadlock, a login attempt).
• Actions — extra pieces of context automatically attached to every captured event, like the SQL text, client hostname, or session ID — without you having to build that correlation yourself.
• Predicates — filters applied before an event is even fully processed, so you’re not paying the cost of capturing thousands of irrelevant events just to throw them away afterward. This is the single biggest reason XEvents is so much cheaper than SQL Trace.
• Targets — where the captured data actually goes: a fast in-memory ring buffer for quick spot-checks, a durable file (or, in Azure SQL, a blob in Azure Storage) for anything you need to keep, or a live streaming target for real-time viewing.

Why Extended Events Beats SQL Trace and SQL Profiler

This isn’t a matter of taste — Microsoft has formally deprecated SQL Trace and SQL Server Profiler and directs everyone toward Extended Events for all monitoring going forward. Here’s why that shift happened.

	SQL Trace / Profiler	Extended Events
Architecture	Separate tracing subsystem layered on top of the engine	Built directly into the engine’s core components
Filtering	Applied late — often after data is already captured	Predicates applied early, before unnecessary processing occurs
Performance overhead	Can be significant, especially with Profiler’s client-side GUI attached	Minimal by design; can run continuously in production with proper configuration
Where it runs	Profiler traces run client-side, adding network and client overhead	Server-side by default; XEvent Profiler in SSMS gives a similar live view without the old overhead
Flexibility	Fixed set of trace columns and events	Hundreds of granular events, customizable actions, multiple target types
Availability in Azure SQL Database	Not available — Profiler/Trace never supported Azure SQL Database	Fully supported, with database-scoped sessions
Status	Deprecated	Actively developed; the recommended tool going forward
Persisting data long-term	Trace files (.trc) on local disk	event_file target — local disk (SQL Server) or Azure Storage blob (Azure SQL DB/MI)
Live viewing experience	Profiler GUI	“Watch Live Data” / XEvent Profiler in SSMS (19.2+), giving a near-identical experience with a fraction of the cost

The practical upshot: with SQL Trace, “let’s watch what’s slow” often meant accepting some amount of Heisenberg effect — the act of watching made things slower. With Extended Events, well-designed sessions can run continuously in production without anyone noticing they’re there.

Extended Events Across SQL Server, Azure SQL Database, and Managed Instance

The core concepts are identical everywhere, but there are real platform differences worth knowing before you write your first session:

	SQL Server	Azure SQL Managed Instance	Azure SQL Database
Session scope	Server-scoped	Both server-scoped and database-scoped (server-scoped recommended for most cases)	Always database-scoped — a session can only see events from its own database
event_file target location	Local disk or Azure Storage blob	Azure Storage blob only	Azure Storage blob only
Authentication to storage	N/A (local disk) or storage key/SAS	Managed identity or SAS credential	Managed identity or SAS credential (managed identity strongly preferred)
XEvent Profiler in SSMS	Supported	Supported	Supported (SSMS 19.2+)

That “always database-scoped” rule for Azure SQL Database trips people up constantly if they’re used to SQL Server’s server-wide visibility — in Azure SQL Database, an event happening in Database A simply cannot show up in a session created against Database B, full stop.

Creating and Managing Sessions: Every Interface You’ll Actually Use

T-SQL — the foundation everything else builds on

This is a session that captures long-running queries (duration over 5 seconds) along with the SQL text and client host, written to a durable file target:

CREATE EVENT SESSION [SlowQueries] ON SERVER
ADD EVENT sqlserver.sql_statement_completed
(
    ACTION (sqlserver.sql_text, sqlserver.client_hostname, sqlserver.session_id)
    WHERE duration > 5000000  -- duration is in microseconds; this is 5 seconds
)
ADD TARGET package0.event_file
(
    SET filename = N'SlowQueries'
)
WITH (MAX_MEMORY = 4096 KB, EVENT_RETENTION_MODE = ALLOW_SINGLE_EVENT_LOSS,
      MAX_DISPATCH_LATENCY = 30 SECONDS, STARTUP_STATE = ON);
GO

-- Sessions are created in a stopped state — you have to start them explicitly
ALTER EVENT SESSION [SlowQueries] ON SERVER STATE = START;

For Azure SQL Database, the syntax is nearly identical, but the event_file target must point to a blob in Azure Storage, and the session is created ON DATABASE instead of ON SERVER:

CREATE EVENT SESSION [SlowQueries] ON DATABASE
ADD EVENT sqlserver.sql_statement_completed
(
    ACTION (sqlserver.sql_text, sqlserver.client_hostname, sqlserver.session_id)
    WHERE duration > 5000000
)
ADD TARGET package0.event_file
(
    SET filename = N'https://mystorageacct.blob.core.windows.net/xevents/SlowQueries.xel'
)
WITH (MAX_MEMORY = 4096 KB, EVENT_RETENTION_MODE = ALLOW_SINGLE_EVENT_LOSS,
      MAX_DISPATCH_LATENCY = 30 SECONDS, STARTUP_STATE = ON);
GO

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

For that blob target to work, the database needs a database-scoped credential, ideally backed by managed identity rather than a storage account key:

CREATE DATABASE SCOPED CREDENTIAL [https://mystorageacct.blob.core.windows.net/xevents]
WITH IDENTITY = 'Managed Identity';

(That should look familiar if you read the earlier article on Azure authentication — this is exactly the “app authenticates via managed identity instead of a stored secret” pattern applied to Extended Events.)

SSMS — the graphical route

1. In Object Explorer, connect to your SQL Server / Azure SQL Managed Instance / Azure SQL Database (connect at the database level for Azure SQL Database).
2. Expand Management → Extended Events → Sessions.
3. Right-click Sessions → New Session Wizard (guided) or New Session (full control).
4. Pick a template (SSMS ships with several — “Query Batch Sampling,” “Locks,” etc.) or build from scratch.
5. On the Events page, search for and add the events you need (e.g., sql_statement_completed, blocked_process_report).
6. On the Global Fields (Actions) page, add context fields like sql_text and client_hostname.
7. On the Filter (Predicate) page, add your conditions (e.g., duration > 5000000).
8. On the Data Storage page, add your target(s).
9. Check Start the event session immediately after session creation, then finish.

XEvent Profiler — the modern Profiler replacement, live in SSMS

For quick, ad-hoc “show me what’s happening right now” investigations, SSMS 19.2+ includes XEvent Profiler, which behaves almost exactly like the old Profiler GUI but is built on Extended Events under the hood (using a live event_stream target rather than writing to disk). Right-click your instance or database in Object Explorer → XEvent Profiler → Launch Standard Session or Launch TSQL Session, and you get a live, scrolling grid of activity — with none of the old Profiler overhead.

Azure Portal

The Azure Portal itself doesn’t have a dedicated Extended Events session designer — this is a T-SQL/SSMS/Azure Data Studio operation. Where the portal does come in is supporting infrastructure: creating the Storage Account and container your event_file target will write to, and setting up the SQL server’s managed identity with the right RBAC role (Storage Blob Data Contributor) on that storage account so the database-scoped credential can actually write files.

Portal steps for that supporting piece:

1. Create or select a Storage Account in the same region as your database.
2. Create a container (e.g., xevents).
3. On your SQL server resource → Identity, enable the system-assigned managed identity if it isn’t already.
4. On the Storage Account → Access Control (IAM) → Add role assignment → Storage Blob Data Contributor → assign to your SQL server’s managed identity.

Azure Data Studio

Azure Data Studio doesn’t (as of this writing) include a full graphical session designer equivalent to SSMS’s wizard, but it’s a perfectly capable tool for running the T-SQL scripts above, and its query results grid is useful for querying captured .xel data once it’s landed.

PowerShell

PowerShell doesn’t have a dedicated cmdlet library for authoring Extended Events sessions (that’s still primarily a T-SQL/SMO task), but it’s commonly used to orchestrate the supporting infrastructure and automation around sessions — provisioning the storage account, assigning RBAC roles, or scheduling session start/stop via automation:

# Create the storage account that will hold XEvent blob targets
New-AzStorageAccount -ResourceGroupName "prod-data-rg" `
  -Name "xeventsstorage01" -Location "eastus2" -SkuName "Standard_LRS"

# Grant the SQL server's managed identity write access to it
$sqlServer = Get-AzSqlServer -ResourceGroupName "prod-data-rg" -ServerName "prod-sql-server"
New-AzRoleAssignment -ObjectId $sqlServer.Identity.PrincipalId `
  -RoleDefinitionName "Storage Blob Data Contributor" `
  -Scope (Get-AzStorageAccount -ResourceGroupName "prod-data-rg" -Name "xeventsstorage01").Id

You’d then run the actual CREATE EVENT SESSION T-SQL via Invoke-Sqlcmd from within the same script if you want fully automated, repeatable deployment of monitoring sessions.

Practical Sessions for Real Problems

Blocking

CREATE EVENT SESSION [CaptureBlocking] ON SERVER
ADD EVENT sqlserver.blocked_process_report
ADD TARGET package0.event_file (SET filename = N'CaptureBlocking')
WITH (MAX_DISPATCH_LATENCY = 5 SECONDS);
GO
ALTER EVENT SESSION [CaptureBlocking] ON SERVER STATE = START;

This relies on the blocked process threshold server configuration being set (in seconds) — without it, blocked_process_report never fires:

EXEC sp_configure 'show advanced options', 1; RECONFIGURE;
EXEC sp_configure 'blocked process threshold', 5; RECONFIGURE;

Deadlocks

CREATE EVENT SESSION [CaptureDeadlocks] ON SERVER
ADD EVENT sqlserver.xml_deadlock_report
ADD TARGET package0.event_file (SET filename = N'CaptureDeadlocks')
WITH (STARTUP_STATE = ON);
GO
ALTER EVENT SESSION [CaptureDeadlocks] ON SERVER STATE = START;

The captured xml_deadlock_report gives you the full deadlock graph — the competing sessions, the resources involved, and which one got picked as the victim.

Slow queries and query timeouts

CREATE EVENT SESSION [SlowAndTimedOutQueries] ON SERVER
ADD EVENT sqlserver.sql_statement_completed
(
    ACTION (sqlserver.sql_text, sqlserver.client_hostname, sqlserver.username)
    WHERE duration > 3000000  -- 3 seconds
)
ADD EVENT sqlserver.attention  -- fires on client-initiated cancels/timeouts
(
    ACTION (sqlserver.sql_text, sqlserver.client_hostname)
)
ADD TARGET package0.event_file (SET filename = N'SlowAndTimedOutQueries')
WITH (MAX_DISPATCH_LATENCY = 15 SECONDS);
GO

The attention event is the one people forget — it’s what fires when a client cancels a query, which is exactly what happens under the hood on a command timeout.

High CPU usage

CREATE EVENT SESSION [HighCpuQueries] ON SERVER
ADD EVENT sqlserver.sql_statement_completed
(
    ACTION (sqlserver.sql_text, sqlserver.session_id)
    WHERE cpu_time > 2000000  -- 2 seconds of CPU time, in microseconds
)
ADD TARGET package0.event_file (SET filename = N'HighCpuQueries')
WITH (MAX_DISPATCH_LATENCY = 30 SECONDS);
GO

Login failures

CREATE EVENT SESSION [LoginFailures] ON SERVER
ADD EVENT sqlserver.error_reported
(
    ACTION (sqlserver.client_hostname, sqlserver.username, sqlserver.session_id)
    WHERE ([severity] = 14 AND [error_number] = 18456)  -- classic login failed error
)
ADD TARGET package0.event_file (SET filename = N'LoginFailures')
WITH (STARTUP_STATE = ON);
GO

This is a genuinely useful early-warning tool for brute-force login attempts or a misconfigured application hammering the server with bad credentials.

Wait statistics and performance bottlenecks

CREATE EVENT SESSION [WaitStats] ON SERVER
ADD EVENT sqlserver.wait_info
(
    ACTION (sqlserver.session_id)
    WHERE duration > 1000  -- only meaningful waits, in milliseconds
)
ADD TARGET package0.histogram
(
    SET filtering_event_name = 'sqlserver.wait_info',
        source_type = 0,
        source = 'wait_type'
)
WITH (MAX_DISPATCH_LATENCY = 30 SECONDS);
GO

The histogram target here is doing something specifically useful — instead of dumping every individual wait event, it buckets and counts occurrences by wait type, giving you an at-a-glance “what is this server spending its time waiting on” summary without wading through raw event data.

Reading captured .xel data back out

SELECT
    event_data.value('(event/@name)[1]', 'varchar(100)') AS event_name,
    event_data.value('(event/@timestamp)[1]', 'datetime2') AS event_time,
    event_data.value('(event/data[@name="duration"]/value)[1]', 'bigint') AS duration_us,
    event_data.value('(event/action[@name="sql_text"]/value)[1]', 'nvarchar(max)') AS sql_text
FROM
(
    SELECT CAST(event_data AS XML) AS event_data
    FROM sys.fn_xe_file_target_read_file('SlowQueries*.xel', NULL, NULL, NULL)
) AS tab
ORDER BY event_time DESC;

Real-World Troubleshooting Scenarios

Scenario 1 — “The app times out randomly, but only during peak hours.” A DBA at a logistics company sets up a SlowAndTimedOutQueries-style session (as above) with STARTUP_STATE = ON, running continuously with a low-overhead file target. Two days later, timeout complaints spike again. Instead of trying to reproduce it live, the DBA queries the .xel files for the exact time window and finds a specific report query, run by one particular client hostname, consistently taking 25+ seconds during the 9 AM shipment batch — a missing index that only becomes painful once the table crosses a certain row count. Index added, timeouts gone.

Scenario 2 — “We’re seeing sporadic deadlocks, but nobody can catch one live.” A retail company enables CaptureDeadlocks with STARTUP_STATE = ON so it survives server restarts and just quietly runs. A week later, a deadlock finally occurs at 3 AM during a batch job nobody was awake to watch. The xml_deadlock_report was already sitting in the event file, showing two batch processes acquiring locks on the same two tables in opposite order — a classic deadlock pattern — and the fix (consistent lock ordering in the batch scripts) gets deployed without ever needing to “catch it in the act.”

Scenario 3 — “Someone is trying to brute-force our SQL login.” A healthcare company’s LoginFailures session shows an unusual spike — hundreds of failed logins in ten minutes, all from one external IP, all trying different usernames. That’s not a typo pattern, that’s a scan. The security team blocks the IP at the firewall and confirms (via the earlier Azure Authentication article’s playbook) that Entra ID–only authentication would have prevented this attack vector entirely, accelerating a migration that had been sitting on the backlog.

Best Practices

• Always specify predicates — capturing everything and filtering afterward defeats the entire performance advantage XEvents has over SQL Trace.
• Prefer the event_file target for anything you need to review later; use ring_buffer only for quick, disposable, in-the-moment checks (it clears on restart and has a hard size cap).
• Set MAX_DISPATCH_LATENCY thoughtfully — very low values (near-real-time delivery) cost more overhead; a value in the 15–30 second range is usually a reasonable balance for most troubleshooting sessions.
• Use EVENT_RETENTION_MODE = ALLOW_SINGLE_EVENT_LOSS for most production sessions — it’s a deliberate trade-off that favors not blocking the engine over guaranteeing zero data loss, which is almost always the right call.
• Set STARTUP_STATE = ON for sessions you want to survive a server restart (like your blocking/deadlock/login-failure baselines) so you’re not caught without data after a routine reboot.
• In Azure SQL Database/MI, keep the storage account in the same region, use managed identity authentication rather than storage keys, and match the storage account’s redundancy tier to your database’s redundancy tier.
• Review who has read access to the storage account or file share holding your .xel files — captured SQL text can include sensitive data, and least-privilege access matters here just as much as anywhere else.
• Periodically clean up old sessions you no longer need — a graveyard of forgotten “just testing this” sessions is a common source of unnecessary overhead and confusion.

Common Pitfalls

1. Forgetting that sessions are created in a stopped state — CREATE EVENT SESSION alone does nothing until you ALTER ... STATE = START.
2. Setting the blocked process threshold server config incorrectly (or not at all) and wondering why blocked_process_report never fires.
3. Using ring_buffer for something you actually need to keep, then losing it on the next restart.
4. Assuming Azure SQL Database sessions can see cross-database activity — they can’t; sessions are always scoped to a single database there.
5. Capturing sql_text on every single statement in a very high-throughput OLTP system without a duration predicate — this can genuinely add up, even with XEvents’ efficiency.
6. Forgetting to set up the database-scoped credential (and the underlying RBAC role) before pointing an event_file target at Azure Storage — the session creation itself may succeed, but writes will fail.
7. Not testing your predicate logic — an overly narrow (or backwards) WHERE clause can silently result in a session that “works” but captures nothing useful.

Performance Considerations

• XEvents’ overhead scales primarily with event frequency and payload size, not with the mere existence of a session — a well-filtered session watching for rare events (deadlocks, login failures) is essentially free to leave running permanently.
• High-frequency events (like sql_statement_completed with no duration filter on a busy OLTP server) are where overhead becomes noticeable — always predicate aggressively.
• The histogram and event_counter targets are cheaper than event_file for aggregate questions (“how many of X happened”) because they don’t serialize full event payloads to storage.
• MAX_MEMORY and EVENT_RETENTION_MODE settings directly trade off “never lose an event” against “never let event capture block query execution” — for production troubleshooting, favoring the latter is almost always correct.

Interview Questions Worth Practicing

1. Why does Microsoft recommend Extended Events over SQL Server Profiler, architecturally?
2. What’s the difference between an event, an action, and a predicate in an Extended Events session?
3. How would you capture deadlock information without relying on someone being online to “catch it live”?
4. Why are Extended Events sessions always database-scoped in Azure SQL Database, and what practical impact does that have?
5. What’s the difference between the ring_buffer and event_file targets, and when would you choose each?
6. How do you configure an event_file target to write to Azure Storage, and what authentication method is recommended?
7. What server configuration has to be set before blocked_process_report will ever fire?

DP-300 Exam Tips

• Know explicitly that SQL Trace and SQL Server Profiler are deprecated and Extended Events is the recommended replacement — this fact itself shows up directly.
• Understand the Azure SQL Database database-scoping rule cold; it’s a favorite “why didn’t this session capture anything” scenario question.
• Be able to identify which target type fits a described monitoring goal — file for durability, ring buffer for quick checks, histogram for aggregate counts.
• Know that Azure SQL Database/MI event_file targets require Azure Storage, and that managed identity is the recommended authentication method for the database-scoped credential — this ties directly into the identity/authentication domain of the exam too.
• Recognize the blocked_process_report event alongside its prerequisite (blocked process threshold configuration) as a pair — the exam likes testing whether you remember dependencies, not just the event name in isolation.

Frequently Asked Questions

Can I use Extended Events on Azure SQL Database the same way I do on-prem SQL Server? Mostly yes — the events, actions, and predicates work the same way. The two real differences are that sessions are always database-scoped, and the event_file target always writes to Azure Storage rather than local disk.

Do Extended Events sessions survive a failover or restart? Only if STARTUP_STATE = ON is set. Without it, a session stops after a restart and has to be manually started again.

Is XEvent Profiler in SSMS the same as the old SQL Server Profiler? It looks and feels similar, but it’s built entirely on Extended Events under the hood (using a live event_stream target), which means it carries none of Profiler’s deprecated architecture or overhead concerns.

Can Extended Events write directly into a SQL table? No — targets write to files, blobs, memory buffers, or histograms, not directly to relational tables. The common pattern is: capture to event_file, then periodically read and load the results into a table using sys.fn_xe_file_target_read_file if you want that data queryable long-term.

How much overhead does a well-designed session really add? For a properly filtered, low-frequency session (deadlocks, login failures, blocking), the overhead is close to negligible — it’s specifically engineered to be safe to run continuously in production, which is the entire point of the redesign from SQL Trace.

What permissions do I need to create a session? ALTER ANY EVENT SESSION for server-scoped sessions, or CONTROL on the database for database-scoped sessions (which, again, is the only kind Azure SQL Database supports).

Decision Matrix: Choosing the Right Session for the Job

You Need To…	Recommended Event(s)	Recommended Target
Catch intermittent blocking	blocked_process_report (with threshold configured)	event_file
Diagnose recurring deadlocks	xml_deadlock_report	event_file, STARTUP_STATE = ON
Find slow queries over a threshold	sql_statement_completed with duration predicate	event_file
Catch client-side timeouts/cancels	attention	event_file
Identify high-CPU queries	sql_statement_completed with cpu_time predicate	event_file
Detect brute-force login attempts	error_reported filtered to error 18456	event_file, STARTUP_STATE = ON
Summarize wait bottlenecks	wait_info	histogram target
Do a quick, disposable live check	Any relevant event	ring_buffer, or XEvent Profiler live view
Persist data long-term in Azure SQL DB/MI	Any relevant event	event_file → Azure Storage blob, via managed identity credential
Get near-real-time visibility without storage	Any relevant event	event_stream (via XEvent Profiler)

The core habit to build: start with the question you’re trying to answer (“why did this time out,” “who’s brute-forcing logins,” “what are we waiting on”), pick the event that directly answers it, filter aggressively with predicates, and choose the cheapest target that still gives you what you need. That’s the whole discipline — everything else in this article is detail in service of that one habit.

Key Takeaways

• Extended Events replaced the deprecated SQL Trace and SQL Server Profiler because of a genuinely better architecture — early filtering via predicates, server-side execution, and minimal overhead by design.
• Events, actions, predicates, and targets are the four building blocks of every session — learn to reason in those terms and any new session becomes easy to design.
• Azure SQL Database sessions are always database-scoped, and event_file targets always write to Azure Storage — two platform differences worth knowing cold.
• Practical, targeted sessions for blocking, deadlocks, slow queries, timeouts, CPU, login failures, and waits can all run continuously in production with negligible overhead when properly filtered.
• Choosing the right target (ring_buffer for disposable checks, event_file for anything durable, histogram for aggregate summaries) matters as much as choosing the right event.