The slow call finished before you opened Activity Monitor. Your first Extended Events session can preserve the next one without tracing every statement. Start with a narrow duration filter and a target you know how to read.
Define the Slow Call You Want
Decide whether the symptom is a slow batch, a stored procedure call, or one statement inside a larger call. sql_batch_completed and rpc_completed cover common application entry points. Their duration fields are measured in microseconds. A one-second threshold therefore uses a value of one million in the predicate. That is a sample threshold, not a performance target for every system.
I ask the application team for the slow path and its normal deadline before choosing the event. What call would you recognize in the output? A capture that records every query will provide many answers to questions nobody asked. Keep the first Extended Events session small enough to understand.
Choose a Safe Target
A ring buffer is useful for a short experiment because it needs no file path. Bound its memory and event count, then read it before stopping the session. Its contents are temporary and can be overwritten. An event_file target is better when you need a durable timeline. Give it a service-writable folder, file size, rollover count, and retention owner.
I use the ring buffer to learn the event shape and a file target for repeatable incident capture. The difference matters after a restart. The ring buffer does not owe you yesterday’s evidence. Check the target permissions before creating a file session, not after the next problem appears.
Create a Focused First Extended Events Session
The sample below captures completed batches above the example duration threshold. It includes SQL text and database name actions and writes to a small ring buffer. Run it once on a test instance with the required privileges. Review the event definition and adjust the threshold for your workload before enabling it in production. The session name identifies its purpose.
I keep SQL text capture limited because the text can contain sensitive literal values. If your application sends parameters, review how much of those values the event exposes. Do not copy raw event files into an open folder. The session is a diagnostic tool with data-handling duties.
CREATE EVENT SESSION SlowBatchWatch ON SERVER
ADD EVENT sqlserver.sql_batch_completed
(
ACTION(sqlserver.sql_text, sqlserver.database_name)
WHERE ([duration] > (1000000))
)
ADD TARGET package0.ring_buffer
(
SET MAX_MEMORY = 1024,
MAX_EVENTS_LIMIT = 100
);
ALTER EVENT SESSION SlowBatchWatch ON SERVER
STATE = START;Make a Controlled Test Call
Run a harmless query in a test database that exceeds your chosen threshold through normal work, or temporarily choose a lower diagnostic threshold under change control. Do not add WAITFOR to production traffic just to create a sample event. Verify that the session is running and that its target receives at least one event. A defined but stopped session records nothing.
I compare the captured time with the application log and the session ID if available. One event shows the plumbing works. It does not prove the chosen predicate will catch every future symptom, especially canceled calls that never complete. Add an attention event when timeout investigation requires it.
Read the Ring Buffer of Your First Extended Events Session
The query below returns the target data as XML for the named active session. Open the XML in SSMS and inspect the event name, timestamp, duration, SQL text, and database. Duration is in microseconds for the completed batch event. Convert units before comparing with application milliseconds. A unit mistake can turn a normal call into a fictitious crisis.
I save one representative event with its context in the runbook. That example helps the next DBA recognize which XML node holds the answer. If the query returns no row, check whether the session started and whether the target name matches.
SELECT
CAST(t.target_data AS xml) AS TargetData
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'SlowBatchWatch'
AND t.target_name = N'ring_buffer';Compare With the Actual Workload
Capture enough events to identify a repeating call, then check its plan, waits, reads, and application context. The slow completed call can be blocked by another transaction or delayed outside SQL Server. One duration number does not name the cause. Use Query Store or a focused follow-up XE session when you need plan history or a different event.
I keep the query text and time together. Removing context and sorting only by duration makes a report dramatic but less useful. The next step should be specific: inspect this plan, this blocker, or this client path.
Move to a File When It Matters
For ongoing capture, create a separate event_file target with bounded files. The SQL Server service must be able to write to its folder. SSMS can open the target data, and sys.fn_xe_file_target_read_file can read the files in T-SQL. Test file rollover and cleanup. Keep the diagnostic path separate from database data and log volumes when storage design requires it.
I review the expected event rate before leaving the session enabled. Lower overhead than a broad trace does not mean zero overhead. A well-filtered file target gives useful history without becoming a new storage incident.
Stop or Own Your First Extended Events Session
After the investigation, stop and remove a temporary session or assign an owner and review date for a permanent one. Record its events, predicate, target, and retention rule. Check active sessions periodically so old experiments do not accumulate. If you change the threshold later, write down why and compare event volume.
Your first Extended Events session succeeds when you can explain what it captures and read a real event from it. I would rather have one narrow session that answers a question than ten mysterious sessions with clever names.
Review Capture Cost
Start with a narrow predicate and a modest target. Watch for dropped events and unexpected file growth under the real workload. A file target preserves evidence across a longer investigation; a ring buffer is useful for a short look and can lose older events. I choose the target based on how long the problem takes to return. Keep file location, rollover, and cleanup details in the runbook. If the session serves one incident, stop it after preserving the relevant output. Your first Extended Events session should teach a safe capture habit, not become a permanent background mystery that nobody knows how to disable.
Related reading on this blog: Capturing Stored Procedure Executions with Extended Events in SQL Server and SQL Profiler vs Extended Events.
An XE session is not useful because it runs, it is useful when its events answer the question you set out to ask.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
