A query is slow right now, and waiting for tomorrow’s report is too late. The SSMS XEvent Profiler gives a live view of selected Extended Events without building a session first.
Know What the Profiler Is
SSMS XEvent Profiler is a live viewer for Extended Events. It offers preconfigured Standard and T-SQL sessions as a quick starting point. It is different from the older SQL Server Profiler trace tool. Check which preconfigured session you open and which events it collects in your SSMS version.
I use it for a short investigation when the symptom is happening now. A live stream can show statements and related event details quickly. It is less suitable as the only record of an overnight issue that nobody watched.
Ask what question you need the events to answer. Which statement is slow? Which application is sending it? A viewer is useful when its captured fields match that question.
SELECT name, event_retention_mode_desc, max_memory
FROM sys.server_event_sessions
WHERE name LIKE N'%Profiler%'
ORDER BY name;Open a Small Live View in XEvent Profiler
Connect to the correct instance in SSMS, expand XEvent Profiler in Object Explorer, and choose the appropriate preconfigured session. Watch the live viewer for a bounded period. Filter the display around the application or statement of interest rather than staring at every event.
I check connection and database context before interpreting the stream. A busy instance can generate many events quickly. A sample statement from another application can look like the problem unless the identity and time match.
The live viewer is a diagnostic window, not a complete history. If the issue is intermittent, plan a focused session with an event_file target so evidence survives when the window closes.
SELECT name, create_time
FROM sys.dm_xe_sessions
ORDER BY name;Read XEvent Profiler Fields With Context
Events can include statement text, duration, CPU, reads, database, and session context depending on the session and event. Inspect the actual captured fields. Do not assume every row contains every value or that the same unit appears in every display column.
I match an event to a known request time and application action. One slow statement can be a symptom of blocking rather than an expensive plan. Pair event details with waits and blockers from DMVs or other monitoring.
Keep sensitive SQL text in mind. Queries can contain literals. Save event files and screenshots under the same access rules as other diagnostic logs. A convenient live view should not create an unprotected data copy.
SELECT session_id, status, wait_type, blocking_session_id,
total_elapsed_time
FROM sys.dm_exec_requests
WHERE session_id <> @@SPID
ORDER BY total_elapsed_time DESC;
Understand XEvent Profiler Capture Cost and Noise
The preconfigured session is designed for a quick view, but any capture has overhead and volume. On a busy server, broad event collection can produce a stream too noisy to analyze. Stop the live investigation when it has answered the question, and review whether the session remains running.
I avoid using a live screen as a performance baseline. It shows what happened during the watch window, not a complete week. Query Store and persisted monitoring can provide the broader pattern. Use the right evidence for the time scale.
A filtered viewer does not necessarily mean the server stopped collecting unfiltered events. Check session definition and target behavior before assuming the workload is narrow. For a repeated problem, build a predicate into a custom session.
SELECT s.name AS session_name, e.name AS event_name
FROM sys.server_event_sessions AS s
JOIN sys.server_event_session_events AS e
ON e.event_session_id = s.event_session_id
ORDER BY s.name, e.name;Build a Focused Session for Repeated Work
A custom Extended Events session can capture only chosen events, actions, and predicates, then write to an event_file target. That is better for an issue that appears after hours or under a particular application. Set retention and file size according to the investigation.
I start from one event that directly answers the question. Then I add context fields needed to correlate it. A giant session collecting everything is harder to review and can add avoidable overhead. A targeted event file supports comparison across runs.
Test the session in a safe window and confirm that the target file receives readable events. Also test stop and cleanup steps. A capture plan is incomplete if nobody knows where the file goes.
SELECT s.name, s.event_retention_mode_desc,
s.max_dispatch_latency
FROM sys.server_event_sessions AS s
ORDER BY s.name;Use the Actual Plan for Query Diagnosis
An event can identify a costly statement, but it does not explain the plan by itself. Capture the query and parameters under the appropriate privacy rules, then inspect its actual plan and runtime metrics. Look for scans, spills, waits, and bad estimates.
I compare events from a normal and slow execution when possible. The same query text can behave differently because parameters, blocking, or data changed. A single large duration is a clue, not a tuning prescription.
Keep the first investigative change reversible. An index or query edit should follow a tested hypothesis. The live profiler helps find the target. It does not tell you which fix is safe.
SELECT TOP (20) session_id, cpu_time, logical_reads,
total_elapsed_time, wait_type
FROM sys.dm_exec_requests
WHERE session_id <> @@SPID
ORDER BY total_elapsed_time DESC;Close the Investigation Cleanly
Record the instance, watch period, session used, filters, and the event that supports the finding. Save only the evidence needed, then stop an ad hoc session if it is no longer required. Check that retained files follow the agreed cleanup policy.
I write a short conclusion that separates observed events from the suspected cause. If blocking was not captured, I do not call it proven. The next capture can add the missing field or event.
XEvent Profiler is a good doorway into live behavior. Use it for a bounded look, then build a focused Extended Events session when the question needs durable, repeatable evidence.
Live events answer what is happening now. They do not give a complete history if you start the viewer after the slow query has finished. Which event and predicate isolate the problem without flooding the session? I define that before watching a busy server. A broad stream can bury the one useful event and add avoidable collection cost.
For a recurring problem, build a named Extended Events session with a deliberate target and retention plan. Save its definition, include actions that identify the query or session when needed, and test that the target survives a restart if the investigation requires persistence. Review the captured payload and permissions before sharing it. A convenient live viewer is a starting point, while a planned session creates repeatable evidence.
Related reading on this blog: Capturing Stored Procedure Executions with Extended Events in SQL Server and SQL Profiler vs Extended Events.
A live event stream is not a diagnosis, it is evidence that helps you choose the next check.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
