What causes slow queries in SQL Server, and how do I fix them?

A slow query in SQL Server is either doing too much work or waiting for something. Compare its CPU time with its elapsed time to find out which. A query that is running slowly has a plan problem: a missing index, a predicate that can't seek, bad estimates or row-by-row code. A query that is waiting is held up by locks, a memory grant, disk, tempdb or the client. Each cause below has a signature you can check in a plan, a wait type or a DMV, and a fix.

Triage: is it running or waiting?

For a statement that is executing now:

SELECT r.session_id, r.status, r.cpu_time AS cpu_ms, r.total_elapsed_time AS elapsed_ms,
       r.total_elapsed_time - r.cpu_time AS approx_wait_ms,
       r.wait_type, r.wait_time, r.blocking_session_id, r.logical_reads, r.dop
FROM sys.dm_exec_requests AS r
WHERE r.session_id = 87;   -- the slow session

For history, compare total_worker_time with total_elapsed_time in sys.dm_exec_query_stats, or avg_cpu_time with avg_duration in Query Store.

• CPU time close to elapsed time: the query is running. Look at the plan and at logical reads.
• Elapsed time far above CPU time: the query is waiting. Find out on what.
• CPU time above elapsed time: a parallel plan. The subtraction doesn't apply, so read the waits directly.

Waits for one session are in sys.dm_exec_session_wait_stats (SQL Server 2016 and later). Waits per query over time are in sys.query_store_wait_stats (SQL Server 2017 and later), which is covered in SQL Server query performance: how to measure and improve it.

SELECT wait_type, waiting_tasks_count, wait_time_ms, signal_wait_time_ms
FROM sys.dm_exec_session_wait_stats
WHERE session_id = 87
ORDER BY wait_time_ms DESC;

Symptom to cause

Symptom	Likely cause
High logical reads, scan with a residual predicate	Missing or unusable index, non-SARGable predicate, implicit conversion
Estimated rows far from actual rows	Stale statistics, table variables, multi-statement functions, parameter sniffing
Fast for some parameter values, slow for others	Parameter sniffing
Key Lookup with a high Number of Executions	Index doesn't cover the query
LCK_M_* waits, non-zero blocking_session_id	Blocking, long transactions
RESOURCE_SEMAPHORE waits, sort or hash warnings	Memory grant problems, spills to tempdb
CXPACKET on short queries, or one busy core on a large query	Too much or too little parallelism
Low reads in STATISTICS IO, high CPU, function in the select list	Scalar UDF
Millions of executions of a trivial statement	Row-by-row code
ASYNC_NETWORK_IO waits	Client consuming rows slowly, too much data returned
PAGELATCH_* waits on database ID 2	tempdb contention
PAGEIOLATCH_*, WRITELOG, SOS_SCHEDULER_YIELD across many queries	Memory, I/O or CPU limits

Causes when the query is running

Missing or unusable index

Recognise it: a scan of a large table whose Predicate discards most rows (compare Number of Rows Read with Actual Number of Rows in the actual plan), a sort feeding a TOP, or a hash join that scans a big table to match a few rows. Logical reads are far out of proportion to the rows returned.

Fix: design one index for the query, or widen an existing one. The workflow, including building online on a big table, is in How do I find and fix missing indexes in SQL Server?

Non-SARGable predicates

Recognise it: the index exists and the plan still scans it. The predicate wraps the column in a function or expression: WHERE YEAR(OrderDate) = 2026, WHERE ISNULL(Status, '') = 'Open', WHERE TotalDue * 1.2 > 100, WHERE Status LIKE '%pen'.

Fix: leave the column bare and move the logic to the other side: WHERE OrderDate >= '2026-01-01' AND OrderDate < '2027-01-01'. When the expression can't be rewritten, add a computed column for it and index that.

Implicit conversions

Recognise it: CONVERT_IMPLICIT around a column in the plan's predicate, and a warning on the root operator. SQL Server converts the operand whose data type has lower precedence, so comparing a varchar column with an nvarchar parameter converts the column on every row. Client libraries that send strings as nvarchar by default are the usual source.

Fix: make the parameter type match the column in the application. In T-SQL, convert the parameter and not the column.

Stale or missing statistics and bad estimates

Recognise it: in the actual plan, Estimated Number of Rows differs from Actual Number of Rows by orders of magnitude, and the operators chosen for the small estimate (Nested Loops, Key Lookup, a small memory grant) run against a large row count. Check how old the statistics are:

SELECT s.name, sp.last_updated, sp.rows, sp.rows_sampled, sp.modification_counter
FROM sys.stats AS s
CROSS APPLY sys.dm_db_stats_properties(s.object_id, s.stats_id) AS sp
WHERE s.object_id = OBJECT_ID(N'Sales.Orders');

Other sources of bad estimates:

• Table variables are estimated at one row unless the database is at compatibility level 150 or later, where table variable deferred compilation uses the actual count (SQL Server 2019 and later).
• Multi-statement table-valued functions get a fixed estimate of 100 rows unless interleaved execution applies (SQL Server 2017 and later, level 140).
• Local variables in a predicate are estimated from average density, not from the histogram.

Fix: UPDATE STATISTICS Sales.Orders WITH FULLSCAN; and compare plans. Keep AUTO_UPDATE_STATISTICS on. Replace table variables that hold many rows with temporary tables, which have statistics.

Parameter sniffing

Recognise it: the same procedure is fast for most calls and very slow for some, or slow from the application and fast when you run it in SSMS. Query Store shows one query with several plans, or one plan with a wide gap between min_duration and max_duration. In the cached plan, Parameter Compiled Value is not representative of the calls that are slow.

Fix: OPTION (RECOMPILE) on the statement, OPTIMIZE FOR, or a plan forced in Query Store. SQL Server 2022 at compatibility level 160 can keep several plans for one query through Parameter Sensitive Plan optimization. The full treatment is in Why is my stored procedure fast one day and slow the next?

Key lookups

Recognise it: an Index Seek joined by Nested Loops to a Key Lookup whose Number of Executions is in the thousands. Logical reads on the table are several times the number of rows returned.

Fix: add the missing columns to the nonclustered index with INCLUDE, or select fewer columns. SELECT * is the usual reason a covering index stops covering. See Reading SQL Server execution plans.

Scalar UDFs and multi-statement TVFs

Recognise it: a query with modest reads in SET STATISTICS IO that burns CPU for minutes. A scalar user-defined function in the select list or WHERE clause is invoked once per row, its internal reads are not reported in the calling query's I/O statistics, and it prevents a parallel plan. A multi-statement TVF shows as a Table Valued Function operator with an estimate of 100 rows.

Fix: rewrite the function as an inline table-valued function (a single RETURN SELECT), or move the logic into the query. SQL Server 2019 at compatibility level 150 inlines eligible scalar UDFs automatically. Check which ones qualify:

SELECT OBJECT_SCHEMA_NAME(m.object_id) AS schema_name, OBJECT_NAME(m.object_id) AS function_name, m.is_inlineable
FROM sys.sql_modules AS m
JOIN sys.objects AS o ON o.object_id = m.object_id
WHERE o.type = 'FN';

Row-by-row code

Recognise it: no single statement is slow. sys.dm_exec_query_stats shows a cheap statement with an enormous execution_count, issued by a cursor, a WHILE loop, an application loop or a trigger that processes one row at a time. sys.dm_exec_trigger_stats shows the cost of triggers.

Fix: replace the loop with one set-based statement: an UPDATE with a join, an INSERT ... SELECT, or a window function in place of a running-total cursor. Write triggers against the whole inserted and deleted tables. In application code, fetch the set in one query and not one query per item.

Causes when the query is waiting

Blocking and long transactions

Recognise it: wait types starting with LCK_M_, and a non-zero blocking_session_id in sys.dm_exec_requests. Follow the chain to the head blocker. If that session is sleeping with open_transaction_count above 0 in sys.dm_exec_sessions, an application opened a transaction and has not committed it.

Fix: commit sooner, and keep user interaction and remote calls out of transactions. Index the columns in UPDATE and DELETE predicates so that the statement locks only the rows it changes. To stop readers and writers blocking each other, enable READ_COMMITTED_SNAPSHOT, which is explained in Transaction isolation levels in SQL Server. NOLOCK has its own costs, covered in Should I use WITH (NOLOCK)? If sessions are being killed as deadlock victims, see Diagnosing deadlocks in SQL Server with Extended Events.

Memory grants and tempdb spills

Recognise it: two opposite problems. A query waiting for its grant shows RESOURCE_SEMAPHORE, and after a long wait fails with error 8645:

SELECT session_id, dop, request_time, grant_time, requested_memory_kb,
       granted_memory_kb, max_used_memory_kb, ideal_memory_kb, wait_time_ms
FROM sys.dm_exec_query_memory_grants
ORDER BY requested_memory_kb DESC;

A NULL grant_time means the query is still queued. A granted_memory_kb many times larger than max_used_memory_kb means an overestimate is taking memory from other queries. The second problem is a grant that is too small: the actual plan shows a warning on a Sort or Hash Match that spilled to tempdb, and total_spills in sys.dm_exec_query_stats is above zero.

Fix: both come from row estimates, so correct the statistics first. Remove the sort with an index in the required order, and select fewer and narrower columns, because the grant is sized from estimated rows multiplied by row size. Memory grant feedback adjusts repeated grants automatically (batch mode in SQL Server 2017, row mode in SQL Server 2019, Enterprise edition). The MIN_GRANT_PERCENT and MAX_GRANT_PERCENT query hints set limits on one statement.

Too much or too little parallelism

Recognise it: CXPACKET and CXCONSUMER waits are normal for parallel plans. They are a problem when short transactional queries go parallel, or when so many queries do that the instance runs out of worker threads and sessions wait on THREADPOOL. The opposite case is a large reporting query that uses one core. Its plan's root operator has a NonParallelPlanReason property that explains why.

Fix: for too much, review cost threshold for parallelism, whose default of 5 Microsoft calls a starting point and not a recommendation, and set MAXDOP as documented for your processor layout. For too little, remove what blocks parallelism. Microsoft lists non-inlineable scalar UDFs and modifications of table variables among the causes, along with a MAXDOP of 1 at the server, database or query level.

Returning too much data: ASYNC_NETWORK_IO

Recognise it: the session waits on ASYNC_NETWORK_IO. SQL Server has rows ready and is waiting for the client to fetch them from the output buffer. The cause is on the client side: an application that processes each row before reading the next, or a result set far larger than anyone needs. SSMS produces this wait itself when it renders millions of rows in the grid.

Fix: return fewer rows and columns, page large results, and have the application read the whole result set before doing slow per-row work. A slow or congested network link is a less common cause. No index or server setting fixes this wait.

tempdb contention

Recognise it: many sessions waiting on PAGELATCH_UP or PAGELATCH_EX (not PAGEIOLATCH), with a wait_resource in sys.dm_exec_requests that starts with 2:, the database ID of tempdb. It appears under workloads that create and drop temporary tables at a high rate.

Fix: give tempdb several data files of equal size. Microsoft's guidance is one per logical processor up to eight, and eight when there are more. SQL Server 2016 and later grow all tempdb files together and use uniform extents without trace flags. SQL Server 2019 adds memory-optimized tempdb metadata (Enterprise edition, off by default) for contention on system tables, and SQL Server 2022 reduces latch contention on allocation pages. Then reduce the churn: fewer temporary objects per call, and fewer spills.

Resource limits: memory, I/O and CPU

Recognise it: many unrelated queries slow down together.

• Memory. PAGEIOLATCH_SH waits on queries that normally run from cache mean pages are being read from disk again. RESOURCE_SEMAPHORE means grant memory is short. Check max server memory first: Why is SQL Server using all my server's memory?
• I/O. Read latency per file from sys.dm_io_virtual_file_stats. The counters accumulate from startup, so compare two snapshots. Microsoft's troubleshooting guidance treats latency consistently above 10 to 15 ms per I/O as a bottleneck. WRITELOG waits point to the log file's storage.
• CPU. SOS_SCHEDULER_YIELD waits and a high share of signal_wait_time_ms mean tasks are queuing for a processor.

SELECT DB_NAME(vfs.database_id) AS database_name, vfs.file_id,
       vfs.io_stall_read_ms / NULLIF(vfs.num_of_reads, 0) AS avg_read_ms,
       vfs.io_stall_write_ms / NULLIF(vfs.num_of_writes, 0) AS avg_write_ms
FROM sys.dm_io_virtual_file_stats(NULL, NULL) AS vfs
ORDER BY vfs.io_stall_read_ms DESC;

Fix: reduce demand before adding hardware. The top queries by total CPU and total logical reads are usually responsible for most of the pressure, and fixing them with the causes above lowers I/O, memory and CPU use together. Add memory, faster storage or cores when the workload is already efficient and still doesn't fit.

Common mistakes

• Fixing a wait with an index, or a plan with hardware. Do the triage first.
• Reading cumulative wait statistics as current. sys.dm_os_wait_stats and sys.dm_io_virtual_file_stats count from startup. Compare snapshots.
• Treating CXPACKET as the problem. It usually marks a large scan that is the real problem.
• Testing in SSMS with literals. You get a different plan from the one the application uses.
• Clearing the plan cache or restarting. It hides parameter sniffing until the next bad compile and discards the evidence.
• Adding NOLOCK to stop blocking. It trades waits for wrong results.
• Updating statistics, seeing an improvement, and stopping. Find out why they went stale, or the problem returns.

Checklist

1. Compare CPU time with elapsed time to decide whether the query is running or waiting.
2. If it is waiting, read the wait type and match it to blocking, memory grants, the client, tempdb or a resource limit.
3. If it is running, get the actual plan and SET STATISTICS IO output.
4. Compare estimated with actual rows, and rows read with rows returned.
5. Check predicates for functions on columns and for CONVERT_IMPLICIT.
6. Look for key lookups, sorts, spills, scalar UDFs and loops.
7. Test with several parameter values before deciding it is fixed.
8. Confirm in Query Store that the query's total CPU, reads and duration fell.

References

• Troubleshoot slow-running queries in SQL Server (Microsoft Learn)
• sys.dm_os_wait_stats, with descriptions of every wait type
• Understand and resolve SQL Server blocking problems
• Troubleshoot slow performance or low memory issues caused by memory grants
• Troubleshoot slow queries resulting from ASYNC_NETWORK_IO
• Troubleshoot slow SQL Server performance caused by I/O issues
• tempdb database
• Scalar UDF inlining
• Intelligent query processing features in detail
• Statistics