A slow SQL Server is rarely a mystery once you stop guessing and start measuring. This guide walks through the seven problems that cause the vast majority of real-world slowdowns, and for each one gives you the symptom, the exact T-SQL to diagnose it, and the fix.
The single biggest mistake teams make is reacting to "the database is slow" by throwing hardware at it or randomly adding indexes. SQL Server ships with rich instrumentation - Query Store and the dynamic management views (DMVs) - that will tell you precisely which queries hurt and why. Find the problem first, then fix the right thing. Everything below assumes a supported version (SQL Server 2016 or later, where Query Store exists).
What this guide covers
- Find the problem before you fix anything
- Blocking & locking
- Missing & duplicate indexes
- Parameter sniffing
- Outdated statistics
- tempdb contention
- Implicit conversions killing seeks
- Plan-cache bloat from ad-hoc queries
- File & auto-growth misconfiguration
- Bad plans: scans vs seeks
- Cheat sheet & where slowdowns come from
1. Find the problem before you fix anything
Perceived slowness almost always traces back to a handful of expensive queries. Rank queries by their total resource cost so you spend effort where it pays off. The classic approach uses sys.dm_exec_query_stats joined to sys.dm_exec_sql_text to pull the actual statement text.
-- Top 20 queries by total worker (CPU) time since last cache flush
SELECT TOP (20)
qs.execution_count,
qs.total_worker_time / 1000 AS total_cpu_ms,
qs.total_worker_time / qs.execution_count / 1000 AS avg_cpu_ms,
qs.total_logical_reads,
qs.total_elapsed_time / 1000 AS total_elapsed_ms,
SUBSTRING(st.text,
(qs.statement_start_offset / 2) + 1,
((CASE qs.statement_end_offset WHEN -1
THEN DATALENGTH(st.text)
ELSE qs.statement_end_offset END
- qs.statement_start_offset) / 2) + 1) AS stmt_text
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
ORDER BY qs.total_worker_time DESC;Swap total_worker_time for total_logical_reads to hunt I/O hogs, or total_elapsed_time for wall-clock pain. The odd / 2 and offset math is because SQL Server stores statement offsets in bytes over a Unicode string. Note that dm_exec_query_stats only reflects what is currently in the plan cache, so a server restart or memory pressure resets it.
Prefer Query Store when you have it
Query Store persists this data across restarts and lets you compare plans over time - it is the modern first stop. Turn it on per database, then query the catalog views.
ALTER DATABASE Sales SET QUERY_STORE = ON
(OPERATION_MODE = READ_WRITE, DATA_FLUSH_INTERVAL_SECONDS = 900);
-- Highest-CPU queries in the last 24 hours, from Query Store
SELECT TOP (20)
qt.query_sql_text,
SUM(rs.count_executions) AS execs,
SUM(rs.avg_cpu_time * rs.count_executions) / 1000 AS total_cpu_ms
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 rsi
ON rsi.runtime_stats_interval_id = rs.runtime_stats_interval_id
WHERE rsi.start_time > DATEADD(HOUR, -24, SYSUTCDATETIME())
GROUP BY qt.query_sql_text
ORDER BY total_cpu_ms DESC;The other question is always "what is SQL Server waiting on?" Wait statistics point you at the category of problem - LCK_* waits mean blocking, PAGEIOLATCH_* means slow reads, PAGELATCH_* in tempdb means allocation contention, CXPACKET/CXCONSUMER means parallelism.
-- Top waits since startup, ignoring benign background waits
SELECT TOP (15) wait_type,
wait_time_ms,
waiting_tasks_count,
wait_time_ms / NULLIF(waiting_tasks_count, 0) AS avg_ms_per_wait
FROM sys.dm_os_wait_stats
WHERE wait_type NOT IN
('CLR_SEMAPHORE', 'SLEEP_TASK', 'BROKER_TASK_STOP',
'XE_TIMER_EVENT', 'DIRTY_PAGE_POLL', 'HADR_FILESTREAM_IOMGR_IOCOMPLETION')
ORDER BY wait_time_ms DESC;Reading the dominant wait type tells you which problem to jump to below:
| Wait type | Means | Go to |
|---|---|---|
LCK_M_* | Sessions waiting on locks | Blocking & locking |
PAGEIOLATCH_* | Waiting on data pages from disk | Indexes / bad plans |
PAGELATCH_* on 2:1:* | tempdb allocation contention | tempdb contention |
CXPACKET / CXCONSUMER | Parallelism skew | Bad plans (often a scan) |
SOS_SCHEDULER_YIELD | CPU pressure / burning cycles | Query rewrite & indexes |
RESOURCE_SEMAPHORE | Waiting for a memory grant | Stats / spilling plans |
Order of work: measure → find the top few queries and dominant wait → fix one thing → measure again. If you want a repeatable routine, our SQL performance tuning checklist lays it out step by step.
2. Blocking & locking
Symptom: queries that are normally fast suddenly hang, timeouts spike, and CPU is actually low - sessions are waiting, not working. Wait stats show LCK_M_* types.
Diagnose: find who is blocking whom right now. sys.dm_exec_requests exposes the blocking_session_id; sys.dm_tran_locks shows the actual locks held.
-- Live blocking chain: blocked request -> blocker
SELECT r.session_id AS blocked_spid,
r.blocking_session_id AS blocker_spid,
r.wait_type,
r.wait_time AS wait_ms,
r.status,
t.text AS blocked_sql
FROM sys.dm_exec_requests AS r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) AS t
WHERE r.blocking_session_id <> 0;
-- What locks are held, and on which object
SELECT l.request_session_id AS spid,
l.resource_type,
l.request_mode,
l.request_status,
OBJECT_NAME(p.object_id) AS object_name
FROM sys.dm_tran_locks AS l
LEFT JOIN sys.partitions AS p ON p.hobt_id = l.resource_associated_entity_id
WHERE l.resource_type <> 'DATABASE'
ORDER BY l.request_session_id;Fix: blocking is a duration problem. Attack it on three fronts:
- Keep transactions short. Never open a transaction, then wait on application logic or user input while holding locks. Do the reads, then
BEGIN TRAN, write,COMMITfast. - Index to reduce scans. A query that scans a table takes locks on far more rows than one that seeks. The right nonclustered index turns a range scan into a handful of key locks.
- Use the right isolation. Read-heavy reporting on an OLTP database is a classic blocker;
READ COMMITTED SNAPSHOTlets readers use row versions instead of blocking writers.
-- Let readers stop blocking writers (test in non-prod: needs tempdb headroom)
ALTER DATABASE Sales SET READ_COMMITTED_SNAPSHOT ON
WITH ROLLBACK IMMEDIATE;A subtle amplifier of blocking is lock escalation. When a single statement acquires more than about 5,000 row or page locks on one table, SQL Server escalates to a full table lock to save memory - and suddenly one modification blocks the entire table. Escalation is usually a signal that the statement is touching far too many rows, which loops right back to indexing and set-size: a well-targeted WHERE with a supporting index rarely escalates.
Blocking that resolves itself is different from a deadlock, where two sessions each hold what the other needs and SQL Server kills one as a victim to break the cycle. If you are seeing error 1205, the fix is different - consistent lock ordering plus an application-side retry loop. Dig into our guide on transactions, locks and deadlocks for those patterns.
3. Missing & duplicate indexes
Symptom: high logical reads, plans full of Index Scan or Table Scan operators, and the optimizer nagging with green "Missing Index" hints in the plan. SQL Server aggregates those suggestions in sys.dm_db_missing_index_details.
-- Missing index suggestions ranked by estimated benefit
SELECT TOP (25)
ROUND(s.avg_total_user_cost * s.avg_user_impact * (s.user_seeks + s.user_scans), 0) AS est_benefit,
d.statement AS table_name,
d.equality_columns,
d.inequality_columns,
d.included_columns
FROM sys.dm_db_missing_index_group_stats AS s
JOIN sys.dm_db_missing_index_groups AS g ON g.index_group_handle = s.group_handle
JOIN sys.dm_db_missing_index_details AS d ON d.index_handle = g.index_handle
ORDER BY est_benefit DESC;Diagnose the other side too. Every index you add slows down writes and consumes storage. Use sys.dm_db_index_usage_stats to find indexes that are written to but almost never read - and true duplicates that just waste maintenance.
-- Indexes that cost writes but earn few reads (candidates to drop)
SELECT OBJECT_NAME(i.object_id) AS table_name,
i.name AS index_name,
us.user_seeks + us.user_scans + us.user_lookups AS reads,
us.user_updates AS writes
FROM sys.indexes AS i
JOIN sys.dm_db_index_usage_stats AS us
ON us.object_id = i.object_id
AND us.index_id = i.index_id
AND us.database_id = DB_ID()
WHERE i.type_desc = 'NONCLUSTERED'
AND us.user_updates > (us.user_seeks + us.user_scans + us.user_lookups) * 10
ORDER BY writes DESC;Watch out: do not blindly apply every missing-index suggestion. The DMV proposes one index per query shape, with columns in a naive order and often huge INCLUDE lists. Consolidate overlapping suggestions into a few well-designed indexes, and confirm the column order matches your real predicates. See SQL indexes for how to design them properly.
4. Parameter sniffing
Symptom: the same stored procedure is fast for some inputs and painfully slow for others, and which is which seems to change after a restart or a plan-cache flush. This is parameter sniffing: SQL Server compiles a plan using the parameter values from the first execution, then reuses that plan for every later call - even when a very different value would want a different plan (a seek for a rare value, a scan for a common one).
Diagnose: compare the compiled parameter to the runtime one in the actual execution plan (the ParameterCompiledValue vs ParameterRuntimeValue attributes), or notice that the same query has wildly different durations by input in Query Store. If forcing a recompile makes it fast, sniffing is confirmed.
Fix - pick the least invasive option that works:
-- (a) Compile for a representative/typical value
SELECT * FROM Orders WHERE CustomerId = @cid
OPTION (OPTIMIZE FOR (@cid = 12345));
-- (b) Optimize for the "average" unknown, not the first sniffed value
SELECT * FROM Orders WHERE CustomerId = @cid
OPTION (OPTIMIZE FOR UNKNOWN);
-- (c) Recompile every run: fresh plan per value (costs CPU on hot paths)
SELECT * FROM Orders WHERE CustomerId = @cid
OPTION (RECOMPILE);Use RECOMPILE for queries with very skewed data that run infrequently; use OPTIMIZE FOR when one plan shape genuinely serves most calls. On SQL Server 2022, Query Store gives you a fourth option: Parameter Sensitive Plan optimization, which can cache multiple plans for one statement automatically. And sometimes the underlying cause is just stale statistics - which is the next problem.
5. Outdated statistics
Symptom: the optimizer picks a bad plan because its row estimates are wrong - you see an estimated 1 row where 500,000 flow through, triggering nested loops and lookups that should have been a hash join and a scan. Common after big data loads or in tables that grow steadily between auto-update thresholds.
Diagnose: check how stale each statistic is and how many rows changed since the last update.
-- Staleness of stats on a table
SELECT s.name AS stat_name,
sp.last_updated,
sp.rows,
sp.rows_sampled,
sp.modification_counter AS rows_changed_since
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('dbo.Orders')
ORDER BY sp.modification_counter DESC;Fix: update the statistics, using FULLSCAN when a sampled estimate is misleading on a skewed column.
-- One table, full scan; or the whole DB with a sensible sample
UPDATE STATISTICS dbo.Orders WITH FULLSCAN;
EXEC sp_updatestats; -- refresh anything that has changedKeep AUTO_UPDATE_STATISTICS on (it is by default). For large tables where the default 20% modification threshold is too slow to trigger, enable trace flag 2371 (or use the improved dynamic threshold that is default on 2016+ under compatibility level 130 and higher). A nightly index-and-stats maintenance job covers the rest.
Two related traps are worth knowing. First, when auto-update fires synchronously it can stall the very query that triggered it while stats recompute - enabling AUTO_UPDATE_STATISTICS_ASYNC lets that query run with the old stats and refreshes in the background. Second, rebuilding an index automatically updates its statistics with a full scan, but a plain reorganize does not, so a maintenance plan that only reorganizes still needs a separate UPDATE STATISTICS step to keep estimates sharp.
6. tempdb contention
Symptom: broad, server-wide slowness under concurrency, with PAGELATCH_UP / PAGELATCH_EX waits on tempdb pages like 2:1:1 (PFS), 2:1:2 (GAM) or 2:1:3 (SGAM). tempdb is a shared resource: temp tables, table variables, sorts, hash spills, version stores and online index builds all land there.
Diagnose: confirm the waits are on tempdb allocation pages and see what is consuming space.
SELECT session_id, wait_type, resource_description
FROM sys.dm_exec_requests
WHERE wait_type LIKE 'PAGELATCH%'
AND resource_description LIKE '2:%'; -- db_id 2 = tempdbFix:
- Multiple equally sized data files. Create 4 to 8 tempdb data files (a common start is one per logical core up to 8), all the same size with the same autogrowth, so allocation spreads across files. SQL Server 2016+ configures this at setup and enables uniform extent allocation automatically.
- Reduce the load. Avoid dumping huge intermediate result sets into temp tables when a set-based query would do. Fix hash and sort spills (see statistics above) - spills write to tempdb.
- Pre-size the files so they do not autogrow during peak load, and put tempdb on fast storage.
-- Add a tempdb data file (repeat for each file, same size)
ALTER DATABASE tempdb
ADD FILE (NAME = tempdev2,
FILENAME = 'T:\tempdb\tempdev2.ndf',
SIZE = 4096MB, FILEGROWTH = 512MB);7. Implicit conversions killing index seeks
Symptom: a perfectly good index exists on the filtered column, yet the plan still shows a scan and the estimates look off. Look for a yellow warning triangle on the SELECT operator: CONVERT_IMPLICIT. This happens when the column's data type does not match the parameter or literal you compare it against, so SQL Server has to convert every row's column value to compare - and a converted column is no longer sargable, so the seek is gone.
The most common culprit is a client sending an NVARCHAR parameter against a VARCHAR column, or a string compared to an INT column.
-- AccountNumber is VARCHAR(20); NVARCHAR has higher precedence,
-- so the COLUMN gets converted -> index scan on every row
DECLARE @acct NVARCHAR(20) = N'AC-40192';
SELECT * FROM dbo.Accounts WHERE AccountNumber = @acct;
-- CustomerId is INT; comparing to a string forces conversion too
SELECT * FROM dbo.Orders WHERE CustomerId = '12345';-- Match the column's type exactly -> clean index seek
DECLARE @acct VARCHAR(20) = 'AC-40192';
SELECT * FROM dbo.Accounts WHERE AccountNumber = @acct;
SELECT * FROM dbo.Orders WHERE CustomerId = 12345;Fix it at the source: make the application pass the correct type (in .NET, set SqlParameter.SqlDbType to VarChar, not the default NVarChar), and align column types across joined tables so foreign-key comparisons never convert. This one is easy to miss and can single-handedly turn a millisecond seek into a multi-second scan.
8. Plan-cache bloat from ad-hoc queries
Symptom: memory that should cache data pages is instead full of thousands of single-use query plans, the buffer pool shrinks, and compilation CPU climbs. This happens when an application builds SQL by string concatenation instead of parameterizing - every literal value produces a distinct query text, so every execution compiles and caches a brand-new plan that is never reused.
Diagnose: measure how much of the plan cache is single-use ad-hoc plans. If a large share of your cache is Adhoc plans with a usecounts of 1, you have bloat.
-- How much cache is wasted on single-use ad-hoc plans?
SELECT objtype,
COUNT(*) AS plan_count,
SUM(CAST(size_in_bytes AS BIGINT)) / 1048576 AS cache_mb,
SUM(CASE WHEN usecounts = 1 THEN 1 ELSE 0 END) AS single_use_plans
FROM sys.dm_exec_cached_plans
GROUP BY objtype
ORDER BY cache_mb DESC;Fix: the real cure is parameterized queries - use sp_executesql with typed parameters, or an ORM that parameterizes, so one plan serves every value. As an immediate, low-risk mitigation you can enable the server-level "optimize for ad hoc workloads" option, which stores only a small plan stub on first execution and caches the full plan only when a query is seen a second time. That alone can reclaim gigabytes on a chatty OLTP server.
-- Stop caching full plans for one-shot queries (safe, reversible)
EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;
EXEC sp_configure 'optimize for ad hoc workloads', 1;
RECONFIGURE;
-- The application fix: parameterize instead of concatenating
EXEC sp_executesql
N'SELECT * FROM dbo.Orders WHERE CustomerId = @cid',
N'@cid INT', @cid = 12345;Concatenating values into SQL is not only a cache problem - it is the classic SQL injection vector too, so parameterizing fixes security and performance in one move. If your codebase does this a lot, it is worth a focused clean-up pass; our SQL developers often start engagements here.
9. File & auto-growth misconfiguration
Symptom: periodic freezes that do not correlate with any one heavy query - the whole database pauses for a moment, then resumes. Or steady write latency on a busy database. The usual causes are tiny auto-growth increments and a transaction log that grows in thousands of small virtual log files (VLFs).
By default a data file grows in small percentage or 64 MB steps and the log in 64 MB steps. On a busy system this means frequent growth events, each of which briefly stalls writes while the file expands. Worse, the log's default growth pattern can leave you with tens of thousands of VLFs, which slows startup, recovery and log backups.
Diagnose: check file sizes, growth settings and the VLF count.
-- File sizes and growth settings for the current database
SELECT name,
type_desc,
size / 128 AS size_mb,
CASE is_percent_growth
WHEN 1 THEN CAST(growth AS VARCHAR) + ' %'
ELSE CAST(growth / 128 AS VARCHAR) + ' MB' END AS growth
FROM sys.database_files;
-- How many VLFs does the log have? (hundreds ok, tens of thousands bad)
SELECT COUNT(*) AS vlf_count
FROM sys.dm_db_log_info(DB_ID());Fix: pre-size files to their expected size so they rarely grow during normal operation, and set a fixed, sensible growth increment (for example 256 MB or 512 MB, never a percentage). If the log already has excessive VLFs, shrink it once and regrow it in a few large steps to consolidate them.
-- Fixed-size growth beats tiny percentage growth
ALTER DATABASE Sales
MODIFY FILE (NAME = Sales_log, SIZE = 8192MB, FILEGROWTH = 512MB);Instant file initialization lets data-file growth skip zeroing the new space, making growth events far cheaper. Grant the "Perform volume maintenance tasks" right to the SQL Server service account - but note it does not apply to the log file, which is always zeroed, so pre-sizing the log matters most.
10. Bad plans: scans vs seeks
Most of the problems above surface the same way in an execution plan: an operator doing far more work than it should. Learning to read a plan ties everything together.
- Index Seek navigates the B-tree to matching rows - cheap, what you usually want for selective predicates.
- Index/Table Scan reads the whole structure. Fine for reports that touch most rows; a red flag when you expected to fetch a few.
- Key Lookup means a nonclustered index found the row but had to jump to the clustered index for extra columns. A few are fine; thousands in a loop means you want a covering index (add the needed columns via
INCLUDE). - Fat arrows between operators show row counts; a thin estimated arrow feeding a fat actual one signals a bad estimate (stats or sniffing).
Pull the cached plan for a heavy query straight from the DMVs and open it in SSMS.
SELECT TOP (10)
qs.total_worker_time / qs.execution_count AS avg_cpu,
qp.query_plan
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) AS qp
ORDER BY avg_cpu DESC;For a deeper walk through reading plans, join order and hint use, see advanced query optimization, and the broader methodology in our performance tuning lesson. If tuning is not your team's day job, our SQL developers do this work daily.
Cheat sheet & where slowdowns come from
Across real production incidents, a small set of causes accounts for most of the pain. The chart below is representative, not a precise measurement - your mix will vary - but it reflects where tuning effort typically pays off.
Notice how little of it is hardware. Buying more RAM or faster disks papers over an unindexed scan for a while, but the query is still wrong - and you will pay again as data grows.
| Problem | Key DMV or tool | First fix |
|---|---|---|
| Find the top offenders | Query Store; dm_exec_query_stats | Rank by CPU / reads, tune the top few |
| Blocking & locking | dm_exec_requests; dm_tran_locks | Shorten transactions; index; RCSI |
| Missing indexes | dm_db_missing_index_details | Add a consolidated, well-ordered index |
| Unused / duplicate indexes | dm_db_index_usage_stats | Drop write-only indexes |
| Parameter sniffing | Actual plan; Query Store | OPTIMIZE FOR / RECOMPILE |
| Outdated statistics | dm_db_stats_properties | UPDATE STATISTICS ... FULLSCAN |
| tempdb contention | PAGELATCH waits on 2:1:* | Multiple equal data files |
| Implicit conversion | Plan warning CONVERT_IMPLICIT | Match parameter type to column |
| Plan-cache bloat | dm_exec_cached_plans | Parameterize; optimize for ad hoc |
| File / auto-growth | sys.database_files; dm_db_log_info | Pre-size; fixed MB growth |
Always test changes in non-production first. Adding an index, flipping READ_COMMITTED_SNAPSHOT, resizing tempdb or forcing a plan can all have side effects at scale - extra write cost, tempdb pressure, or a plan that helps one query and hurts another. Validate against production-like data and volume, then roll out during a maintenance window and keep watching Query Store afterward.
Work the list in order: measure first, fix the biggest offender, then re-measure. That loop - not guesswork or bigger hardware - is what turns a slow SQL Server back into a fast one.