Early in my career I attached SQL Profiler to a production instance to catch a slow query. Ninety seconds later the box went from two thousand batches a second to connection timeouts, and I learned the hard way that Profiler's row-by-row GUI stream is not passive observation — it is a synchronous firehose that drags the engine along with it. The postmortem had one line of action: never again on production. Extended Events is what replaced it, and it is genuinely lighter — but "lighter" is a range, not a guarantee. A badly written XE session with fat predicates and expensive actions can hurt a busy instance almost as reliably as Profiler did. The difference is that XE gives you the tools to be cheap, if you use them on purpose.
This is how I actually use Extended Events on production: the anatomy that matters, a session I run without fear, how to read what it captures, and the specific knobs that decide whether your tracing is free or expensive. Everything here applies to SQL Server 2016 through 2022 and Azure SQL.
Why did Extended Events replace Profiler, and what is the anatomy?
Because the engine was built around XE and Profiler was bolted on the outside. An XE session is defined inside the engine: you declare events to capture (sql_statement_completed, rpc_completed, wait_info), predicates to filter them, actions to attach extra data, and targets to receive the results. The engine evaluates the predicate at the moment the event fires and discards non-matching events immediately — that short-circuit is the whole performance story. A session that only collects statements over 500 milliseconds evaluates one cheap predicate per statement and drops nearly everything before allocating anything. Profiler, by contrast, streamed events toward a consumer and filtered late; the fire came before the filter.
Deprecated is the other word to internalize. SQL Trace and Profiler have been on the deprecation list for years, and the engine's own diagnostics — the system_health session, the deadlock graph you get out of the box, Query Store's plumbing — all ride XE. Even if you loved Profiler, the direction of travel ended years ago. The deadlock graphs in my deadlock graph reading notes come from the system_health XE session, not from any trace.
What does a production-safe session actually look like?
Capture completed statements above a duration floor, filter as early as possible, attach only cheap actions, and write to a ring buffer or event file — never to the event stream toward a live viewer on a busy box. This is the template I start from:
CREATE EVENT SESSION [slow_statements] ON SERVER
ADD EVENT sqlserver.sql_statement_completed
(
ACTION (sqlserver.sql_text, sqlserver.client_app_name,
sqlserver.database_name)
WHERE [duration] > 500000 -- microseconds
AND [sqlserver].[is_system] = 0
)
ADD TARGET package0.ring_buffer
(SET max_events_limit = 1000, max_memory = 4096)
WITH (EVENT_RETENTION_MODE = ALLOW_SINGLE_EVENT_LOSS,
MAX_DISPATCH_LATENCY = 30 SECONDS);
ALTER EVENT SESSION [slow_statements] ON SERVER STATE = START;Read the choices, because each one is a cost decision. The duration predicate is in microseconds, not milliseconds — that one has bitten everyone exactly once. The is_system filter drops engine noise. The actions list is short and cheap: sql_text, app name, database name are near-free; anything involving plan handles converted to showplan XML is not, and we will get to that. EVENT_RETENTION_MODE = ALLOW_SINGLE_EVENT_LOSS tells the engine it may drop events under pressure rather than slow the workload to keep your trace complete — that is the correct priority order on a production box. MAX_DISPATCH_LATENCY of thirty seconds means events can sit buffered briefly before flush, which smooths the write cost. The defaults push the other way on both, which is why sessions written by the GUI wizard deserve a second look before you start them.
How do I read what the session captured?
Ring buffer contents come back as one XML blob you shred with nodes(); event files are read with sys.fn_xe_file_target_read_file. The ring buffer is my default for short captures — a few hours of slow statements — because it needs no file plumbing and dies with the session. The shredding query is boilerplate you will write once and keep forever:
SELECT
n.value('(event/@name)[1]', 'nvarchar(100)') AS event_name,
n.value('(event/@timestamp)[1]', 'datetime2') AS event_time,
n.value('(event/data[@name="duration"]/value)[1]', 'bigint') / 1000.0 AS duration_ms,
n.value('(event/action[@name="database_name"]/value)[1]', 'nvarchar(128)') AS db,
n.value('(event/action[@name="client_app_name"]/value)[1]', 'nvarchar(256)') AS app,
n.value('(event/action[@name="sql_text"]/value)[1]', 'nvarchar(max)') AS sql_text
FROM (
SELECT CAST(target_data AS xml) AS target_xml
FROM sys.dm_xe_sessions AS s
JOIN sys.dm_xe_session_targets AS t
ON t.event_session_address = s.address
WHERE s.name = N'slow_statements'
AND t.target_name = N'ring_buffer'
) AS x
CROSS APPLY target_xml.nodes('RingBufferTarget/event') AS e(n)
ORDER BY event_time DESC;Two ring buffer caveats from experience. It is lossy by design: with ALLOW_SINGLE_EVENT_LOSS and a 1,000-event cap, a burst of slow statements evicts older ones, and the target quietly tells you how many it dropped in its XML header. And it is volatile: stop the session and restart the instance, and the buffer is gone. For anything you need to survive restarts or keep for days, switch the target to event_file with a size cap and rollover files, then read it back with fn_xe_file_target_read_file — same shredding pattern, persistent storage, and the session survives instance restarts if you create it with STARTUP_STATE = ON.
Which knobs make a session expensive instead of free?
Three, in descending order of how often I have seen them hurt someone. First, fat predicates and no thresholds: a session capturing sql_statement_completed with no duration filter on a box running five thousand statements a second is allocating an event, evaluating actions, and dispatching to a target five thousand times a second. The predicate is not a nicety; it is the load limiter. Filter on duration, on database, on client app — whatever narrows the stream closest to the event source.
Second, expensive actions. The action list runs after the predicate matches, which softens the cost, but some actions are costly per invocation: anything that walks the call stack, and especially collecting execution plans via query_plan-related actions or capturing sqlserver.query_post_execution_showplan. That last one is the Profiler-shaped trap wearing an XE badge — a live plan per matching statement on a hot workload is an overhead bomb, and the GUI makes it two clicks away. For plan data on production, let Query Store collect it asynchronously; my Query Store notes cover why that pipeline is built for exactly this.
Third, no_event_loss and causality tracking on hot paths. EVENT_RETENTION_MODE = NO_EVENT_LOSS inverts the priority order: under memory pressure the engine will slow the workload to preserve your trace, which is the correct choice almost never — a compliance-grade audit capture, briefly, on a sized box. TRACK_CAUSALITY correlates events across tasks and is wonderful for reconstructing a single gnarly execution and measurably bad at high event rates; use it for targeted reproductions, not standing sessions. And before any of this hand-rolled capture for aggregate waits, check whether the built-in system_health session or the wait analysis from my wait statistics triage notes already answers the question — capturing wait_info on a busy server is one of the most expensive sessions you can write, and dm_os_wait_stats gives you most of the answer for free.
What about the system_health session?
It is running on your instance right now, by default, and it already captures several things people build custom sessions for: deadlocks with their full graphs, sessions that hit severe errors, long latch warnings, and a few others depending on version. Before authoring anything, look at what system_health has already recorded — the deadlock graph for the incident that happened last night is frequently already sitting in its ring buffer, and shredding it is the same XML pattern as above with the session name swapped. I extend rather than replace it: custom sessions for the specific questions (slow statements, a particular app's errors), system_health for the baseline safety net.
Capturing production traces with MonPG when SQL Server support lands
The honest truth about tracing is that it is episodic: you capture when you are hunting something. What should be continuous are the aggregates — slow statement counts per duration bucket, error counts by severity, the health of your standing sessions themselves, because a session that failed to start after a restart is a silent hole in your safety net. MonPG monitors PostgreSQL in production today; SQL Server support is on the roadmap and in active development, and the SQL Server monitoring (coming soon) page carries the current status. Until it ships, a scheduled query against dm_xe_sessions to confirm your sessions are running, plus the ring buffer shredder above wired to a job, covers the gap.