Capture Slow SQL Server Requests With Focused Extended Events

An intermittent slow request is difficult to diagnose after it disappears. An execution plan saved from a different run may not explain whether the request spent time on CPU, reads, or waiting. A focused Extended Events session can preserve useful evidence without attempting to record every detail of every statement on the server.

Start with a concrete question: which completed requests in one database take at least one second? This is narrower than tracing all execution plans and broad enough to identify candidates for deeper analysis. The example targets a conventional SQL Server instance; database-scoped cloud sessions and storage targets require different configuration.

Define the capture before starting it

Check event metadata on the actual instance rather than guessing units from a column name.

SELECT object_name, name, type_name, description
FROM sys.dm_xe_object_columns
WHERE object_name IN (N'rpc_completed', N'sql_batch_completed')
  AND name IN (N'duration', N'cpu_time');

For the completed RPC and SQL batch events used here, duration is expressed in microseconds, so 1,000,000 represents one second. Do not assume every timing field in every Extended Event uses the same unit. Preserve the unit when exporting results to a spreadsheet or monitoring system.

The following session filters to database ID 5. Replace that ID with the result of DB_ID for the intended database. Also replace the example Windows path with an existing directory writable by the SQL Server service account, and use a session name that does not collide with an existing session.

-- SQL Server instance example: replace database_id 5 and the file path.
CREATE EVENT SESSION AxialSlowRequestsDemo ON SERVER
ADD EVENT sqlserver.rpc_completed (
    ACTION(sqlserver.client_app_name, sqlserver.database_name,
           sqlserver.session_id, sqlserver.sql_text)
    WHERE ([duration] >= (1000000) AND [sqlserver].[database_id] = (5))
),
ADD EVENT sqlserver.sql_batch_completed (
    ACTION(sqlserver.client_app_name, sqlserver.database_name,
           sqlserver.session_id, sqlserver.sql_text)
    WHERE ([duration] >= (1000000) AND [sqlserver].[database_id] = (5))
)
ADD TARGET package0.event_file (
    SET filename = N'D:\XEvents\AxialSlowRequests.xel',
        max_file_size = (20), max_rollover_files = (4)
)
WITH (MAX_MEMORY = 4096 KB,
      EVENT_RETENTION_MODE = ALLOW_SINGLE_EVENT_LOSS,
      MAX_DISPATCH_LATENCY = 5 SECONDS,
      STARTUP_STATE = OFF);
ALTER EVENT SESSION AxialSlowRequestsDemo ON SERVER STATE = START;

RPC events cover calls such as procedure execution from a driver; batch events cover submitted SQL batches. Applications differ in how they send work, so capturing both avoids an obvious blind spot. It does not make them interchangeable measures or guarantee that all event durations can be added without considering their scope.

The event-file target has bounded file size and rollover. Old files can be removed as rollover advances, so this is a diagnostic window rather than a permanent archive. STARTUP_STATE OFF prevents this temporary investigation from automatically resuming after a restart.

ALLOW_SINGLE_EVENT_LOSS accepts the possibility of dropped events in exchange for diagnostic behavior suited to this example. The capture must not be described as an exact audit. Monitor session health and losses if completeness matters to the investigation.

Read enough evidence to select the next test

Use the same target path to inspect recorded events.

SELECT object_name, CAST(event_data AS xml) AS EventXml
FROM sys.fn_xe_file_target_read_file(
    N'D:\XEvents\AxialSlowRequests*.xel', NULL, NULL, NULL
);

The XML contains the event timestamp, event-specific fields, and the selected actions. Interpret timestamps consistently, normally as UTC, when correlating with application logs. Read duration, CPU time, logical reads, and the relevant batch or statement text according to each event's schema.

A long duration with relatively little CPU suggests investigating waits, blocking, storage, or other delays; it does not identify one cause by itself. High logical reads can point toward excessive data access, while high CPU can justify plan and expression analysis. Correlate a specific request with the plan and parameter context that actually produced it.

Completed events do not explain a request that is still running forever. For an active incident, inspect current requests and waits separately. Client-side network time and rendering time are also outside the completed SQL event's execution duration.

Treat captured SQL text as potentially sensitive. It can include literals and application data. Limit file access, collect only the actions needed, and apply a deliberate retention period after the investigation. Avoid attaching raw event files to broad support threads without reviewing their contents.

Stop, verify, and preserve the conclusion

After reproducing the issue or reaching the time budget, stop and remove the temporary session.

ALTER EVENT SESSION AxialSlowRequestsDemo ON SERVER STATE = STOP;
DROP EVENT SESSION AxialSlowRequestsDemo ON SERVER;

Dropping the session definition does not delete the event files. Preserve the relevant evidence and then manage the files under the chosen retention policy. A later investigation should not accidentally read unrelated older files because its wildcard is too broad.

Do not begin with high-volume statement events and full execution-plan capture across the whole instance. Expand the session only to answer a specific unresolved question, and measure its impact. A filter that is narrow in theory may still match thousands of requests during an incident.

Record the database, time window, threshold, event types, losses, and resulting hypothesis. Then make one targeted change or collect the next necessary measurement. The value of Extended Events is the connection between a real slow request and a testable explanation, not simply the existence of another trace file.

Technical references: Microsoft Learn: Extended Events quick start · Microsoft Learn: CREATE EVENT SESSION · Microsoft Learn: Read event files.