A SQL Server database does not wear out with age. When a system that once responded quickly becomes slow, the usual explanation is that the workload has changed while the design and operating practices have not. More rows are being searched, more users are competing for the same resources, and queries are being asked to do work their original authors never anticipated.
Growth changes the cost of familiar work
A query that scans fifty thousand rows may be harmless. The same access pattern against fifty million rows can dominate storage throughput and memory. Data growth also changes selectivity. A status value that was once evenly distributed may now have one overwhelmingly common value, which makes an older index or execution plan less useful.
Start by measuring growth by table and index, not only by database file size. File size includes free space and does not explain where active data lives. Compare row counts, index sizes, and daily transaction volume with a known healthy period. This establishes whether the slow operation is doing more work or doing the same work less efficiently.
Plans can change while the query text stays the same
SQL Server chooses an execution plan using statistics, parameter values, available indexes, optimizer behavior, and the resources it estimates the query will need. A statistics update, deployment, restart, index change, or unusual first parameter can produce a different cached plan. Sometimes that plan is excellent for one input and poor for another.
This is why a plan captured during an incident is more valuable than a plan generated later in a test window. Use Query Store when available to compare runtime history and plan changes. Look at actual row counts versus estimates, join choices, memory grants, spills, key lookups, and warnings. Do not force an old plan merely because it once ran faster; first determine why the optimizer stopped choosing it and whether the older plan remains safe for the full range of parameters.
Indexes and statistics drift away from the workload
Applications evolve. New reports add filters, integrations introduce batch updates, and once-rare search combinations become routine. The original indexes may no longer support the important paths. At the same time, years of reactive tuning can leave overlapping indexes that increase write cost and consume buffer pool space.
- Review high-cost reads and writes together; an index that helps one report may tax every transaction.
- Check statistics age and modification patterns, especially on large or unevenly distributed tables.
- Confirm that maintenance jobs complete successfully rather than assuming their schedules mean the work occurred.
- Evaluate unused-index evidence across a representative business cycle, because restart counters erase useful history.
Fragmentation is rarely a complete diagnosis. Rebuilding indexes can temporarily improve performance because it also refreshes statistics and reorganizes pages, but that does not prove fragmentation caused the original problem. Measure the query behavior before and after any maintenance change.
Concurrency exposes design problems
A system can look healthy with ten users and fail with one hundred even when no individual query changed. Longer transactions hold locks for longer. Missing indexes cause updates to scan more rows. Chatty application patterns increase network round trips and extend transaction scope. Background jobs collide with business traffic that did not exist when their schedules were chosen.
During an incident, capture the head blocker, the statements waiting behind it, transaction age, wait types, and the application or job responsible. A list of blocked sessions without the head of the chain is incomplete. Also distinguish blocking from CPU pressure and storage latency; all three can make a screen appear frozen, but each requires a different response.
Infrastructure limits may finally be visible
Storage, memory, CPU, and network capacity matter, but utilization must be interpreted in context. High CPU can be the result of avoidable scans or compilations. Storage latency can come from a checkpoint, an undersized log path, or a reporting query that requests far more data than users consume. Memory pressure may be caused by an instance-level configuration problem or excessive query grants rather than insufficient installed memory.
| Question | Evidence |
|---|---|
| What became slower? | Named operation, normal duration, incident duration, and time window |
| What was SQL Server waiting on? | Session waits, blocking chain, CPU, storage latency, and runnable tasks |
| Did the plan change? | Query Store history and captured execution plans |
| Did the workload change? | Row growth, execution counts, parameter mix, releases, and job history |
Restore control with baselines and ownership
The durable fix is usually a sequence: stabilize the immediate regression, correct the query or index design, then add the monitoring and operating discipline that would have revealed the drift earlier. Record normal duration and resource use for critical operations. Retain Query Store history long enough to cover the business cycle. Alert on failed backups and jobs, unexpected file growth, sustained blocking, and capacity trends.
Slowdown over time is evidence of changed conditions, not proof that SQL Server needs to be replaced.
Hardware or a platform migration may still be justified, but those decisions should follow a workload diagnosis. Otherwise the organization pays to run the same inefficient access patterns on a larger machine and loses the opportunity to correct the real constraint.