Extended Events vs. the Old SQL Trace and Profiler

SQL Trace and its GUI front-end, Profiler, have been deprecated since SQL Server 2012, and Microsoft has said plainly that a future version will remove them entirely. Most environments still have someone who opens Profiler out of habit anyway, it's familiar, it's visual, and Extended Events took years to get a comparably approachable UI. The habit is worth breaking: the underlying engine SQL Trace runs on is genuinely more expensive to run, not just older.

What's Actually Different

	SQL Trace / Profiler	Extended Events
Architecture	Rowset-based, every event is packaged and delivered even to consumers that don't want it	Lightweight event framework built into the engine itself; consumers subscribe only to what they need
Overhead	Higher, especially with Profiler's live GUI attached and pulling events over the network in real time	Designed for production use; asynchronous targets keep the hot path nearly untouched
Filtering	Applied after the event is captured	Predicates evaluated before the event is even fully collected, cheaper by design
Where output goes	Trace file, trace table, or the Profiler UI's live grid	Ring buffer (in-memory), event file (disk), or several other targets, selectable independently of collection
Future support	Deprecated since SQL Server 2012, scheduled for eventual removal	The actively developed path forward, including Azure SQL where Trace/Profiler mostly don't exist at all

A Practical Session: Catching Long-Running Queries

This is the Extended Events equivalent of the classic Profiler "Duration > threshold" trace, without the Profiler GUI's live-streaming overhead:

CREATE EVENT SESSION [LongRunningQueries] ON SERVER
ADD EVENT sqlserver.sql_statement_completed
(
    ACTION (sqlserver.sql_text, sqlserver.username, sqlserver.client_hostname)
    WHERE ([duration] > 5000000)   -- microseconds; 5,000,000 = 5 seconds
)
ADD TARGET package0.event_file
(
    SET filename = N'LongRunningQueries.xel',
        max_file_size = 50,
        max_rollover_files = 5
)
WITH (MAX_MEMORY = 4096 KB, EVENT_RETENTION_MODE = ALLOW_SINGLE_EVENT_LOSS,
      MAX_DISPATCH_LATENCY = 30 SECONDS);
GO

ALTER EVENT SESSION [LongRunningQueries] ON SERVER STATE = START;
GO

-- Read it back later, no live GUI attached to the production server needed
SELECT
    event_data.value('(event/@timestamp)[1]', 'datetime2')      AS EventTime,
    event_data.value('(event/action[@name="sql_text"]/value)[1]', 'nvarchar(max)') AS SqlText,
    event_data.value('(event/data[@name="duration"]/value)[1]', 'bigint') / 1000000.0 AS DurationSeconds
FROM sys.fn_xe_file_target_read_file('LongRunningQueries*.xel', NULL, NULL, NULL)
CROSS APPLY (SELECT CAST(event_data AS XML) AS event_data) AS x;

The predicate on duration is evaluated by the engine before the event is fully materialized, exactly the filtering-before-capture design that makes this cheaper than an equivalent Profiler trace watching every statement go by and filtering afterward.

Where the GUI still helps: SSMS's "Watch Live Data" window on an Extended Events session gives you the same live-grid experience Profiler users are used to, it's just attached to a session that was cheap to define and stop, rather than a Profiler trace that stays expensive for as long as it's running.

Common Gotchas

Ring buffer targets aren't durable. They're fast and convenient for quick ad-hoc troubleshooting, but the data disappears on restart and is capped in size, use an event file target for anything you need to keep.
Forgetting to stop a session. Extended Events sessions, like traces, keep running (and keep consuming resources, however small) until explicitly stopped; an old troubleshooting session left running for months is a common audit finding.
Too many events, not enough predicates. Extended Events' low overhead is a design goal, not a guarantee, a session capturing every statement on a busy server with no filter can still add up. Filter at the event level whenever possible.
Assuming feature parity is 1:1. Some Profiler event classes map cleanly to Extended Events; others require combining a different set of events and actions to reconstruct the same information. Budget time for the mapping when migrating an existing trace-based monitoring setup.

The Bottom Line

Extended Events isn't just a newer UI wrapped around the same collection mechanism, the underlying architecture is genuinely cheaper to run, which is exactly why it's the only path forward in Azure SQL and the reason SQL Trace has been on the deprecation list for over a decade. The syntax is more verbose than dragging columns into a Profiler template, but a session defined once in T-SQL is also easier to version, deploy consistently, and tear down cleanly than a GUI trace nobody remembers configuring.

← Back to Blog