Why SQL Server Queries Suddenly Become Slow

A query that worked fine yesterday is suddenly taking 30 seconds. No code changed. Nothing obvious happened. This is one of the most common SQL Server complaints — and it has several well-understood causes.

The good news is that SQL Server provides enough diagnostic information to identify most of these causes. The challenge is knowing where to look and what the information means. This article covers the six most common causes of sudden query performance regression.

1. Parameter Sniffing

SQL Server compiles execution plans based on the parameter values present at compile time. If the plan was compiled for a customer with 10 orders and is being reused for a customer with 10 million orders, the plan will be wrong for the large customer. This is parameter sniffing — and it is the most common cause of sudden query regressions.

The mechanism is straightforward: SQL Server caches execution plans to avoid recompiling the same query on every execution. When a stored procedure or parameterised query is first executed, SQL Server generates an execution plan based on the actual parameter values at that point. That plan is cached and reused for subsequent executions — regardless of whether the new parameter values would warrant a different plan.

Signs of parameter sniffing:

• The query is fast with some parameter values, slow with others
• Running DBCC FREEPROCCACHE fixes the problem — temporarily
• The problem returns, often after a SQL Server restart or plan cache pressure event
• The query runs fast in isolation but slow under normal conditions

Investigation: Compare the cached plan with a freshly compiled plan. Execute the slow version of the query with OPTION (RECOMPILE) appended and compare the execution time. If the recompile version is significantly faster, parameter sniffing is the cause. In Query Store, you can compare multiple plans for the same query and identify when a plan change caused the regression.

Solutions:

• OPTION (RECOMPILE) on the query — forces a fresh compile on every execution (acceptable overhead for infrequently run queries)
• OPTIMIZE FOR (@param UNKNOWN) — instructs the optimiser to use average statistics rather than the sniffed value
• Query Store plan forcing — pin the good plan
• Stored procedure rewrite using local variables — prevents SQL Server from sniffing the parameter directly

2. Stale Statistics

SQL Server uses statistics to estimate how many rows a query will return at each step of the execution plan. If the statistics are stale — because large amounts of data were loaded, deleted, or changed — the estimates will be wrong. Wrong estimates produce wrong plans. Wrong plans produce slow queries.

Statistics become stale gradually under normal database operation. SQL Server has an auto-update statistics feature, but it triggers only after a threshold number of rows have changed — 20% of the table for smaller tables, lower percentages for very large tables (with trace flag 2371 or database compatibility level 130+). If you load a large dataset, the statistics may not update automatically until the threshold is crossed.

Signs of stale statistics:

• The query started performing differently after a large data load or purge
• The estimated row counts in the execution plan look nothing like the actual row counts
• Updating statistics manually resolves the problem

Investigation: Check when statistics were last updated using sys.stats joined to sys.objects, or use DBCC SHOW_STATISTICS on specific tables. In the execution plan, compare estimated rows vs actual rows at each operator — large discrepancies point to stale statistics.

Solution: UPDATE STATISTICS on the affected tables. Consider scheduling regular statistics maintenance, especially after large data operations. Enable auto-update statistics asynchronously (AUTO_UPDATE_STATISTICS_ASYNC) on active databases to prevent statistics updates from blocking query execution.

3. Index Fragmentation or Missing Indexes

Indexes become fragmented over time as data is inserted, updated and deleted. Heavy DELETE operations in particular cause significant fragmentation. A heavily fragmented index increases I/O because SQL Server must read more pages to find the same amount of data.

Missing indexes are a separate problem: if your data patterns have changed — new query types, new parameters, new table sizes — the existing indexes may not cover what is now being queried. SQL Server will do a table scan or clustered index scan where an index seek would be possible.

Investigation:

• sys.dm_db_index_physical_stats — index fragmentation by table and index
• sys.dm_db_missing_index_details and sys.dm_db_missing_index_groups — missing index suggestions
• Execution plan operators — look for Index Scan vs Index Seek; scans on large tables are expensive

Solution: Rebuild or reorganise fragmented indexes based on fragmentation level (reorganise below 30%, rebuild above 30% is a common rule of thumb — adjust based on your maintenance window). Review missing index suggestions carefully before adding indexes; each index adds write overhead and should serve real query needs.

4. Lock Contention and Blocking

If another session is holding a lock on a table or row that your query needs, your query waits. Under heavy concurrent load, this can cause queries that normally complete in milliseconds to wait for seconds — or to time out entirely.

Blocking is not always visible at the query level. A query that appears to be running slowly may actually be waiting. The execution time includes the wait time.

Signs of blocking:

• Queries are fast when run in isolation but slow during business hours
• Timeouts occur intermittently, at predictable times of day
• The problem is worse during periods of high concurrent activity

Investigation: Use sys.dm_exec_requests to see currently running sessions and their wait types. LCK_M_* wait types indicate locking. sys.dm_os_waiting_tasks shows what each session is waiting for and which session holds the blocking lock. If the problem is intermittent, Extended Events or a blocking monitoring script can capture blocking events as they occur.

Solution: Investigate the blocking session — what is it doing, and why is it holding locks for a long time? Common causes include long-running transactions, batch operations without appropriate commit frequency, and missing indexes that cause table scans with broad lock acquisition. Read Committed Snapshot Isolation (RCSI) is worth evaluating for high-concurrency OLTP workloads — it eliminates most read-vs-write blocking by providing row versioning.

5. TempDB Pressure

Many SQL Server operations use TempDB — sorting large result sets, hash joins and hash aggregates, spool operators, temporary tables, table variables, and certain CTE evaluations. If TempDB is under pressure — allocation page contention, version store growth, or insufficient space — queries that use TempDB will slow down.

TempDB allocation page contention is a specific problem on servers with many CPUs. TempDB uses a small number of allocation pages (pages 2, 3, and 4) that are accessed frequently. On busy servers with a single TempDB data file, these pages become a contention point.

Signs of TempDB pressure:

• Performance problems occur across many different, unrelated queries simultaneously
• PAGELATCH_EX or PAGELATCH_SH waits on TempDB allocation pages (database_id 2)
• TempDB data files growing unexpectedly
• Execution plans showing spill warnings

Investigation: Check wait statistics for PAGELATCH_EX waits on TempDB pages. Check TempDB file configuration — how many data files, are they equal size? Check for spill operations in execution plans (sort and hash warnings). Review version store size if RCSI is enabled.

Solution: Add TempDB data files — the recommendation is one per logical CPU up to eight. All data files should be the same size with the same autogrowth settings. Review queries generating large temporary datasets and consider whether they can be rewritten to reduce TempDB usage.

6. Data Volume Growth

A query plan that was acceptable for 100,000 rows may be entirely wrong for 10 million rows. As tables grow, the optimiser's estimates change and previously reasonable plans become unreasonable. A nested loops join that was fine on a small dataset becomes catastrophic at scale.

This type of regression is gradual but often produces a sudden apparent change — there is a point at which the volume crosses a threshold where the existing plan fails completely rather than just underperforming.

Investigation: Check table row counts and historical growth patterns. Identify queries doing full table scans on large tables. Look for nested loops joins on tables that have grown significantly.

Solution: Index review — do existing indexes still cover the query patterns as data has grown? Query rewrite to change join strategy or reduce dataset size before joining. For very large tables, consider archival or partitioning.

What to Do When a Query Suddenly Regresses

When a query regresses suddenly, work through these steps:

• Check statistics currency — when were statistics last updated on the tables involved?
• Compare cached plan vs recompile — run the query with OPTION (RECOMPILE) and compare performance
• Check for blocking — is the query actually waiting rather than running?
• Review recent changes — deployments, data loads, configuration changes, SQL Server restarts
• Review Query Store if enabled — it records plan changes with timing
• Check wait statistics for the relevant time period

The key rule in all SQL Server troubleshooting: something always changed. Query performance regressions do not occur in a vacuum. The investigation is to establish what changed, and when.