“Open Profiler and watch everything” is a request I challenge. Replacing Profiler with Extended Events starts by naming the question you need answered. A focused session can capture the right events with less overhead and clearer retention.
Before Replacing Profiler, Ask What the Trace Was Meant to Find
Profiler and SQL Trace could show batches, calls, errors, and locks, but a broad live trace makes it hard to find the one event that matters. Write the investigation as a question: which call exceeds a duration threshold, which error appears, or who blocks a session? That question determines the XE event and filter. Do not recreate every Profiler column simply because it was on an old template.
I ask for a sample symptom before creating a session. What exact observation would close the incident? Once the answer is clear, the event list becomes much smaller. A small capture is easier to review and easier on the server.
Map Familiar Questions to XE When Replacing Profiler
For completed application calls, consider sql_batch_completed and rpc_completed. For statement-level detail, use an appropriate statement event only when the extra volume is justified. Use error_reported for errors and blocked_process_report for configured blocking reports. Add actions such as session_id, database_name, or sql_text where they help connect an event to a user path. Check each event’s fields before assuming a Profiler column has a direct equivalent.
When replacing Profiler, I keep a mapping note for the old trace purpose, not for every old checkbox. Some questions are better answered by Query Store or DMVs. XE is a capture tool, not a reason to collect the same fact three times.
Start From a Template, Then Narrow
SSMS includes Extended Events creation tools and quick XEvent Profiler sessions. They are useful starting points when you need to see event shape. Inspect the generated session before leaving it running. Change broad event choices, actions, and targets to fit your question. A default template is a starting page, not the finished monitoring design.
I save the final definition in the team’s operations documentation with its owner and stop date. A session created during an incident can outlive the incident if nobody owns it. The server does not politely forget to collect data when the meeting ends.
Use Predicates to Control Volume
Filter on duration, database, client application, or another event field when supported. Filter as early as the event definition allows, rather than collecting everything and filtering only in a report. Avoid using sensitive SQL text unless the investigation requires it and retention controls are clear. In a busy system, a narrow predicate can make the difference between useful evidence and a large noisy file.
I test a proposed session under representative load and watch event rate and dropped events. A claim that XE is lower overhead than a broad Profiler trace is not permission to create an unlimited session. Measure your capture on the target instance.
Choose the Right Target
An event_file target gives durable, bounded files and is usually the practical choice for an incident you need to review later. Set file size and rollover limits, and place files where the SQL Server service can write them. A ring buffer works for short, low-volume exploration but can be overwritten or lost after a stop or restart. Document which limitation is acceptable.
The query below lists defined server sessions and whether they are configured to start with the service. Inspect names before adding another session with the same purpose. I prefer one focused session with a clear owner over a stack of unnamed captures.
SELECT
name,
startup_state,
event_retention_mode_desc,
max_dispatch_latency
FROM sys.server_event_sessions
ORDER BY name;Discover Event Names on Your Instance
Extended Events metadata is available from system dynamic management views. Use the next query to confirm that common event names exist on the connected version. That is safer than copying a session definition from a different engine version and assuming every event and field is present. Inspect event columns and actions in SSMS when building the final capture.
I test the session definition in a nonproduction environment when possible. An event name compiling is one check; a useful payload under the actual workload is another. Record sample event XML before deploying broadly.
SELECT
name,
description
FROM sys.dm_xe_objects
WHERE object_type = 'event'
AND name IN
(N'sql_batch_completed',
N'rpc_completed',
N'error_reported',
N'blocked_process_report')
ORDER BY name;Read the Event as a Timeline
XE events include timestamps and data fields. Sort them by time, group by session or activity, and compare them with application logs. A completed event tells you what finished; it does not automatically show every wait inside the call. Use a suitable event or DMV when you need that deeper detail. Keep the query text, client identity, and duration tied to the same event.
I look for a repeating pattern before calling one event the cause. A long call can be a victim of blocking, not the blocker. The trace should help you ask a narrower next question. That is progress, even if it is not the final fix.
Retire the Old Trace Safely When Replacing Profiler
Inventory server-side traces and scheduled Profiler captures before removing them. Identify the report or alert that reads each output. Replace that consumer with an XE-based path and test it. Then stop the old capture and watch for missing diagnostics during the agreed transition. Do not leave both tools collecting the same broad workload indefinitely.
Replacing Profiler is useful when it moves you from an unbounded habit to a question-driven capture. I keep a short catalog of approved XE sessions with their event purpose, target, retention, and owner. The next incident starts with a known tool rather than a new floodlight.
Keep a Small Session Library
Save reviewed Extended Events definitions for common questions such as blocking, deadlocks, slow calls, and errors. Include predicates, target paths, rollover rules, and stop instructions. I test each definition on a representative workload before broad use. A template with no filter can collect far more than the Profiler trace it replaced. Review event names and fields after upgrades. The next DBA should be able to capture the same evidence without building a new session during an outage. Repeatability matters more than a familiar interface. Keep the library short enough that every session has a clear purpose and owner.
Related reading on this blog: SQL Profiler vs Extended Events and Capturing Stored Procedure Executions with Extended Events in SQL Server.
Replacing a trace is not changing the file type, it is capturing the evidence your question actually needs.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
