Slow SQL Server: diagnose blocking, waits and expensive queries

Start with the symptom, not a setting

“SQL Server is slow” can mean one query, an entire application, a blocking period, or a saturated host. Before changing an index or configuration, record the time, duration, database, user action, frequency, latest change, and whether other applications are affected.

This guide focuses on troubleshooting a performance incident. It complements the broader SQL Server health assessment, which also covers backups, integrity, capacity, SQL Agent, security, and high availability. The queries below read engine state; they do not apply automatic fixes.

• Microsoft — Troubleshoot an entire SQL Server instance that is slow ↗

Lightweight collection or deeper capture?

Point-in-time DMVs are quick and valuable during an incident, but a snapshot after recovery may show nothing. Query Store retains per-database history. Extended Events fits intermittent events. An actual execution plan, an overly broad trace, or very frequent collection can add measurable load, so pilot it and constrain duration, filters, and retention.

1. Capture active requests and their waits

Run this collection while the slowdown is occurring. wait_type shows what a request is currently waiting for; blocking_session_id points to a session holding it up. Compare CPU, reads, and elapsed time against the same unit of work rather than a universal threshold.

A waiting session is not necessarily the cause. The goal is to connect the wait to SQL text, database, application, and blocking chain.

SELECT r.session_id, s.login_name, s.host_name, s.program_name,
       DB_NAME(r.database_id) AS database_name,
       r.status, r.command, r.wait_type, r.wait_time,
       r.blocking_session_id, r.cpu_time, r.total_elapsed_time,
       r.logical_reads, r.reads, r.writes,
       txt.text AS batch_text
FROM sys.dm_exec_requests AS r
JOIN sys.dm_exec_sessions AS s ON s.session_id = r.session_id
OUTER APPLY sys.dm_exec_sql_text(r.sql_handle) AS txt
WHERE r.session_id <> @@SPID
  AND s.is_user_process = 1
ORDER BY r.total_elapsed_time DESC;

• Microsoft — sys.dm_exec_requests ↗

2. Find the head blocker, not only the victims

Short blocking is normal in a transactional engine. It becomes an incident when duration and impact exceed what the application tolerates. A session can block without an active request, for example while holding an open transaction; that is why dm_exec_sessions and dm_exec_connections complement dm_exec_requests.

Do not terminate a session merely because it is a blocker. Confirm the owner, transaction, rollback implications, business impact, and estimated rollback time. KILL can extend an outage when a large transaction must be rolled back.

SELECT blocked.session_id AS blocked_session_id,
       blocked.blocking_session_id AS blocker_session_id,
       blocked.wait_type, blocked.wait_time, blocked.wait_resource,
       DB_NAME(blocked.database_id) AS database_name,
       blocked_text.text AS blocked_batch,
       blocker.login_name AS blocker_login,
       blocker.host_name AS blocker_host,
       blocker.program_name AS blocker_program,
       blocker.open_transaction_count,
       blocker_text.text AS blocker_last_batch
FROM sys.dm_exec_requests AS blocked
JOIN sys.dm_exec_sessions AS blocker
  ON blocker.session_id = blocked.blocking_session_id
OUTER APPLY sys.dm_exec_sql_text(blocked.sql_handle) AS blocked_text
LEFT JOIN sys.dm_exec_connections AS bc
  ON bc.session_id = blocker.session_id
OUTER APPLY sys.dm_exec_sql_text(bc.most_recent_sql_handle) AS blocker_text
WHERE blocked.blocking_session_id > 0
ORDER BY blocked.wait_time DESC;

• Microsoft — Understand and resolve SQL Server blocking ↗

3. Read waits as a direction, not a verdict

sys.dm_os_wait_stats aggregates waits across engine threads. Its counters restart with the database engine or an explicit reset. A total accumulated over several weeks may not describe this morning's incident, so capture two snapshots and calculate a delta over a representative interval.

A leading wait does not dictate one fix. PAGEIOLATCH can reflect physical reads caused by a poor plan as well as slow storage; WRITELOG can come from log latency, very frequent transactions, or an availability topology. CXPACKET and CXCONSUMER require a look at plans, duration, CPU, and work distribution before changing MAXDOP.

SELECT wait_type, waiting_tasks_count, wait_time_ms, signal_wait_time_ms
INTO #waits_before
FROM sys.dm_os_wait_stats;

WAITFOR DELAY '00:00:10';

SELECT TOP (20) a.wait_type,
       a.waiting_tasks_count - b.waiting_tasks_count AS tasks_delta,
       a.wait_time_ms - b.wait_time_ms AS wait_ms_delta,
       a.signal_wait_time_ms - b.signal_wait_time_ms AS signal_ms_delta
FROM sys.dm_os_wait_stats AS a
JOIN #waits_before AS b ON b.wait_type = a.wait_type
WHERE a.wait_time_ms > b.wait_time_ms
ORDER BY wait_ms_delta DESC;

DROP TABLE #waits_before;

• Microsoft — sys.dm_os_wait_stats ↗

4. Correlate I/O waits with files

dm_io_virtual_file_stats counters are cumulative. The averages below can hide spikes; use them to compare files, workload types, and comparable periods. Also capture a delta during the incident and correlate it with operating-system or storage-platform metrics.

High latency on a data file does not prove storage is the root cause. An unnecessary scan, missing index, or regressed plan can generate the I/O volume. For the log, also examine transaction size, commit frequency, growth, and replicas.

SELECT DB_NAME(vfs.database_id) AS database_name,
       mf.type_desc, mf.physical_name,
       vfs.num_of_reads, vfs.num_of_writes,
       CAST(1.0 * vfs.io_stall_read_ms / NULLIF(vfs.num_of_reads, 0)
         AS decimal(18,2)) AS avg_read_ms,
       CAST(1.0 * vfs.io_stall_write_ms / NULLIF(vfs.num_of_writes, 0)
         AS decimal(18,2)) AS avg_write_ms,
       vfs.sample_ms
FROM sys.dm_io_virtual_file_stats(NULL, NULL) AS vfs
JOIN sys.master_files AS mf
  ON mf.database_id = vfs.database_id AND mf.file_id = vfs.file_id
ORDER BY avg_read_ms DESC;

• Microsoft — sys.dm_io_virtual_file_stats ↗

5. Rank expensive queries still in cache

dm_exec_query_stats can rank cumulative work by CPU, duration, and reads. Examine both totals and averages: a rare heavy query has a different priority from a small query executed millions of times.

Rows exist only while the plan remains in cache. Restart, recompilation, eviction, or a cache flush changes the observed period. Do not clear cache to “fix” slowness: it removes evidence and forces recompilation.

SELECT TOP (25) DB_NAME(txt.dbid) AS database_name,
       qs.execution_count, qs.creation_time, qs.last_execution_time,
       qs.total_worker_time / 1000.0 AS total_cpu_ms,
       qs.total_worker_time / NULLIF(qs.execution_count,0) / 1000.0 AS avg_cpu_ms,
       qs.total_elapsed_time / NULLIF(qs.execution_count,0) / 1000.0 AS avg_duration_ms,
       qs.total_logical_reads,
       qs.total_logical_reads / NULLIF(qs.execution_count,0) AS avg_logical_reads,
       SUBSTRING(txt.text,
         (qs.statement_start_offset / 2) + 1,
         ((CASE qs.statement_end_offset WHEN -1 THEN DATALENGTH(txt.text)
           ELSE qs.statement_end_offset END - qs.statement_start_offset) / 2) + 1
       ) AS statement_text
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS txt
ORDER BY qs.total_worker_time DESC;

• Microsoft — sys.dm_exec_query_stats ↗

6. Use Query Store to compare before and after

Query Store retains text, plans, and interval-based statistics in every database where it is active. SQL Server 2022 enables it by default for new databases, but an upgraded or restored database may have another state. Check actual_state_desc, desired_state_desc, capture mode, size, and retention before concluding there is no history.

The query below weights averages by execution count. It uses UTC and must run in the affected database. Adjust the window to the incident; one Query Store interval can contain multiple executions and plans.

SELECT actual_state_desc, desired_state_desc, readonly_reason,
       current_storage_size_mb, max_storage_size_mb,
       query_capture_mode_desc, stale_query_threshold_days
FROM sys.database_query_store_options;

SELECT TOP (25) q.query_id, p.plan_id,
       SUM(rs.count_executions) AS executions,
       CAST(SUM(rs.avg_duration * rs.count_executions) /
         NULLIF(SUM(rs.count_executions),0) / 1000.0 AS decimal(18,2)) AS avg_duration_ms,
       CAST(SUM(rs.avg_cpu_time * rs.count_executions) /
         NULLIF(SUM(rs.count_executions),0) / 1000.0 AS decimal(18,2)) AS avg_cpu_ms,
       CAST(SUM(rs.avg_logical_io_reads * rs.count_executions) /
         NULLIF(SUM(rs.count_executions),0) AS decimal(18,2)) AS avg_logical_reads,
       qt.query_sql_text
FROM sys.query_store_query_text AS qt
JOIN sys.query_store_query AS q ON q.query_text_id = qt.query_text_id
JOIN sys.query_store_plan AS p ON p.query_id = q.query_id
JOIN sys.query_store_runtime_stats AS rs ON rs.plan_id = p.plan_id
JOIN sys.query_store_runtime_stats_interval AS i
  ON i.runtime_stats_interval_id = rs.runtime_stats_interval_id
WHERE i.end_time > DATEADD(hour, -2, SYSUTCDATETIME())
GROUP BY q.query_id, p.plan_id, qt.query_sql_text
ORDER BY avg_duration_ms DESC;

• Microsoft — Monitor performance with Query Store ↗
• Microsoft — Query Store best practices ↗

7. Separate blocking from deadlocks

Blocking waits for a resource to be released. A deadlock is a cycle that SQL Server resolves by selecting a victim and returning error 1205. Looking only at active sessions after the error is therefore insufficient.

Since SQL Server 2012, Microsoft recommends the xml_deadlock_report event instead of SQL Trace or Profiler. The system_health Extended Events session captures it by default. In SSMS, open Management > Extended Events > Sessions > system_health, view target data, and filter for xml_deadlock_report. Preserve the graph, objects, lock order, plans, and application code; retry logic can hide the symptom without fixing the cycle.

• Microsoft — SQL Server deadlocks guide ↗
• Microsoft — system_health Extended Events session ↗

8. Read a plan without jumping to conclusions

Query Store plan forcing can quickly stabilize a regression, but it is a production change. Test it, record the query_id and plan_id, monitor forcing failures, and plan sp_query_store_unforce_plan as rollback. Temporary stabilization does not remove the need to fix the cause.

• Compare estimated and actual rows at key operators; a large gap can indicate statistics, correlation, or parameter sensitivity.
• Look for scans, lookups, sorts, spills, implicit conversions, warnings, and memory grants, then relate them to actual volume.
• Compare multiple Query Store plans for the same query_id and the time of the change.
• Check parameter values, cardinality variation, and compatibility level before concluding parameter sniffing.
• SQL Server 2022 can use Parameter Sensitive Plan optimization in supported conditions; it does not replace data-model analysis.
• Treat a missing-index suggestion as a hypothesis: review existing indexes, write cost, space, and overlap.

• Microsoft — Parameter Sensitive Plan optimization ↗

9. Minimum permissions and version differences

Use a dedicated diagnostic identity and grant only the required visibility. State permissions can reveal SQL text, object names, and sometimes sensitive values. A diagnostic export needs an owner, protected location, and short retention.

• Microsoft — Dynamic management views and functions ↗

Shortcuts that often make the incident worse

A repeatable incident plan

• T0 — record symptom, users, time, database, query, and recent change.
• T+5 — capture active requests, blocking chain, waits, and host metrics without changing state.
• T+15 — compare Query Store before and during the incident; retrieve deadlocks and relevant events.
• Triage — decide whether the priority is availability, contention, plan, capacity, or an external dependency.
• Change — one correction at a time, representative test, success criteria, and rollback.
• After — measure duration, CPU, reads, errors, and concurrency; retain before-and-after evidence.
• Prevention — automate lightweight collection and schedule deeper analysis outside the incident.

Move from symptom to durable correction

Reliable troubleshooting connects a user experience to a timeline, query, plan, resource, and cause. It avoids universal thresholds and mass remediation. When the issue is critical, preserve evidence and rollback capability first.

A health assessment then broadens the view to backups, integrity, jobs, capacity, and configuration. Keeping these scopes distinct prevents a performance emergency from hiding a more serious recovery or security risk.

• Article — SQL Server health assessment with T-SQL and PowerShell →
• Service — Infrastructure architecture and modernization →