What to Collect When SQL Server Is Slow

SQL Server diagnostic data is most useful while the slowdown is happening. Collect a small, timestamped set of evidence before restarting services or changing settings, because those actions can erase the explanation.

Start With Time and Waits

Record when the problem started, which application is affected, and what users are waiting to finish. Include the time zone. Ask whether everything is slow or one operation is slow. Those answers keep a server-wide investigation from overlooking a single blocked transaction.

SELECT SYSDATETIMEOFFSET() AS captured_at,
       sqlserver_start_time
FROM sys.dm_os_sys_info;

SELECT TOP (20)
    wait_type, waiting_tasks_count,
    wait_time_ms, signal_wait_time_ms
FROM sys.dm_os_wait_stats
ORDER BY wait_time_ms DESC;

Wait statistics are cumulative since startup or the last reset. Their largest value doesn’t automatically describe the current incident. Save a second sample after a known interval and compare differences. Don’t reset the counters to make the arithmetic easier.

Background waits can dominate an unfiltered list. Learn the meaning of the relevant wait types before treating them as faults. Waiting is part of normal operation. The question is which waits increased during the affected workload and whether that explains the user’s delay.

Capture the Active Requests

SELECT
    session_id, DB_NAME(database_id) AS database_name,
    status, command, cpu_time, total_elapsed_time,
    logical_reads, reads, writes,
    wait_type, wait_time, blocking_session_id,
    sql_handle, plan_handle
FROM sys.dm_exec_requests
WHERE session_id <> @@SPID;

This is a snapshot of work still running. Completed requests disappear from this view. Repeat the capture if the symptom is intermittent and retain each timestamp. Use the required diagnostic permissions, including VIEW SERVER PERFORMANCE STATE on newer SQL Server releases where applicable.

Elapsed time and CPU time answer different questions. A request can spend much of its elapsed time waiting. Logical reads describe page accesses, not a count of physical disk operations. Keep the column names and units with exported results so another person can interpret them.

Follow Blocking to Its Owner

SELECT
    r.session_id AS waiting_session,
    r.blocking_session_id,
    r.wait_type, r.wait_resource,
    s.login_name, s.host_name, s.program_name,
    s.open_transaction_count
FROM sys.dm_exec_requests AS r
LEFT JOIN sys.dm_exec_sessions AS s
    ON s.session_id = r.blocking_session_id
WHERE r.blocking_session_id > 0;

A positive blocking session ID points to another session, but the first blocker you see can itself be blocked. Follow the chain. The head session can be sleeping with an open transaction, so it may not appear as an active request.

Capture the owning application and transaction context before considering cancellation. Client-reported host and program names are clues rather than authenticated identity. Killing a session can trigger rollback and additional waiting. Decide with the incident owner rather than using KILL as a diagnostic probe.

Preserve the Query and Plan

Save the relevant query text and available plan while their handles remain valid. Query text can contain sensitive values, so keep captures in an approved location. Don’t post a complete production batch into a public troubleshooting forum merely because the plan looks complicated.

SELECT
    r.session_id, t.text AS batch_text,
    p.query_plan
FROM sys.dm_exec_requests AS r
OUTER APPLY sys.dm_exec_sql_text(r.sql_handle) AS t
OUTER APPLY sys.dm_exec_query_plan(r.plan_handle) AS p
WHERE r.session_id <> @@SPID
  AND r.session_id > 50;

The cached plan returned here isn’t an actual plan with runtime row counts for this execution. Use appropriate runtime capture when it is needed and acceptable. If Query Store is enabled, preserve the relevant history and compare the affected period with a known good period.

Focus the query on the session under investigation on a busy instance. Gathering every plan repeatedly can create its own overhead. Collect enough evidence to answer a question, then decide the next question.

Check File IO in Context

SELECT
    DB_NAME(v.database_id) AS database_name,
    f.type_desc, f.physical_name,
    v.num_of_reads, v.io_stall_read_ms,
    v.num_of_writes, v.io_stall_write_ms
FROM sys.dm_io_virtual_file_stats(NULL, NULL) AS v
JOIN sys.master_files AS f
    ON f.database_id = v.database_id
   AND f.file_id = v.file_id;

Use interval differences here too. A lifetime average can hide a short storage stall or exaggerate an old event. Compare read and write counts with their corresponding stall times. Keep data files and log files separate because their workloads and consequences differ.

A Task Manager screenshot supplies useful host context, but it doesn’t identify a blocked session or explain a bad estimate. Low CPU can coexist with severe waiting. High CPU can reflect legitimate throughput. Correlate host observations with the engine evidence and the affected application.

Leave an Evidence Package

Save the timestamped captures, the exact symptom, and any changes already made. Record which observations are snapshots and which are interval totals. Include gaps in coverage.

An honest partial capture is easier to use than a confident story built from unrelated measurements. Keep the collection scripts as well as their output. A result without the query that produced it can leave important filters invisible.

Then make one targeted change with a way to compare the outcome. Preserve the before evidence. I want the next person to understand the chosen action, even if the server appears healthy when they arrive.

A diagnostic capture is not a screenshot of concern, it is evidence tied to a specific period of work.

This post was rewritten from scratch in September 2026. The original, published on 2012-08-26, was a short announcement about something that no longer exists. The address is the same, the subject is now something worth keeping.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.