Why is SQL Server suddenly slow?

When a SQL Server system that was fine yesterday is slow today, there is pressure to do something quickly. The most useful thing you can do in the first half hour is collect evidence before anything changes, because several common “fixes” destroy the information needed to find the cause. This is the sequence we follow when a business-critical database suddenly slows down.

Confirm it really is the database

Start by narrowing the problem. Is everything slow, or one screen, report or batch job? Is it every user, or one site or application server? When did it start? A single slow process points towards a query or plan problem; everything slowing at once points towards blocking, resource pressure or infrastructure.

Then check whether SQL Server is actually busy. If the application servers are running flat out or waiting on another service, the database may be innocent. Inside SQL Server, ASYNC_NETWORK_IO is a useful referee: it means SQL Server has results ready and is waiting for the client to accept them. Sessions in that wait usually indicate an application processing large result sets row by row, or a slow network, rather than a slow database.

A query that runs quickly in Management Studio but slowly from the application does not prove the application is at fault either. The two connections often use different SET options (ARITHABORT is the usual one), so they get separate cached plans that may have been compiled for different parameter values.

What changed?

Performance rarely changes for no reason. Build a timeline of what happened around the time the slowdown began:

Deployments. New application releases, changed stored procedures, ORM upgrades and new reports all change the queries the server receives.
Data growth. A table that grows past a certain size can tip the optimiser into a different plan, or turn a tolerable scan into an intolerable one.
Statistics. From compatibility level 130, automatic statistics updates trigger after the smaller of 500 plus 20% of rows, or the square root of 1,000 times the row count, in modifications. That suits large tables better than the old rule, but tables with ascending keys such as dates still get queries for recent rows the histogram knows nothing about.
Plan regressions and parameter sensitivity. A plan compiled for an unusual parameter value is reused for every other value, and a recompile, statistics update or plan eviction can swap a good plan for a bad one with no code change. SQL Server 2022 added Parameter Sensitive Plan optimization at compatibility level 160, which can keep several plans per query, but only for equality predicates.
Maintenance jobs. Index maintenance, integrity checks and backups that have drifted into business hours, or a job that updates statistics with default sampling straight after an index rebuild has produced full-scan statistics.
Index changes. A dropped or disabled index, or a new one that changed plan choices for other queries.
Patching and upgrades. Cumulative updates, Windows updates, driver or firmware changes, and especially a raised database compatibility level after an upgrade, which changes optimiser behaviour including cardinality estimation.
VM and storage changes. A host migration, a move to a different storage tier, a reduced vCPU or memory allocation, a busier host, snapshot or backup activity at the hypervisor, or a power plan that throttles the CPU.

Where to look first

The dynamic management views below are read-only. On SQL Server 2022 and later they need VIEW SERVER PERFORMANCE STATE; on earlier versions, VIEW SERVER STATE. Most of their counters reset when the service restarts, which is one of several reasons not to restart yet.

Current activity and blocking

Look at what is running now. sys.dm_exec_requests shows each active request with its wait type, how long it has been waiting, CPU and elapsed time, and a blocking_session_id if another session is holding it up.

SELECT r.session_id,
       r.status,
       r.blocking_session_id,
       r.wait_type,
       r.wait_time AS wait_ms,
       r.cpu_time AS cpu_ms,
       r.total_elapsed_time AS elapsed_ms,
       r.logical_reads,
       DB_NAME(r.database_id) AS database_name,
       t.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 t
WHERE s.is_user_process = 1
  AND r.session_id <> @@SPID
ORDER BY r.total_elapsed_time DESC;

If many requests show LCK_M_* waits and a non-zero blocking_session_id, you have a blocking problem, and the priority is finding the session at the head of the chain rather than tuning individual queries. sys.dm_os_waiting_tasks gives the same picture per task, with the locked resource in resource_description.

Wait statistics

sys.dm_os_wait_stats records cumulative time spent in each wait type since the service started. The totals are dominated by harmless background waits and by history, so take two snapshots a few minutes apart while the system is slow and compare them. As a rough guide, PAGEIOLATCH_* and WRITELOG point towards storage or large reads, LCK_M_* towards blocking, RESOURCE_SEMAPHORE towards queries queuing for memory, and SOS_SCHEDULER_YIELD towards CPU pressure. Waits describe symptoms rather than causes: heavy PAGEIOLATCH_SH is as often a query reading far too much data as it is slow disks.

Expensive queries and Query Store

sys.dm_exec_query_stats aggregates CPU, reads and duration for plans currently in cache. Its rows disappear when a plan is removed from cache, so check creation_time before drawing conclusions.

SELECT TOP (10)
       qs.total_worker_time / 1000 AS total_cpu_ms,
       qs.execution_count,
       qs.total_logical_reads / NULLIF(qs.execution_count, 0) AS avg_logical_reads,
       qs.creation_time,
       qs.last_execution_time,
       SUBSTRING(t.text, qs.statement_start_offset / 2 + 1,
         (CASE qs.statement_end_offset
               WHEN -1 THEN DATALENGTH(t.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 t
ORDER BY qs.total_worker_time DESC;

Query Store suits the “what changed” question better, because it keeps plan and runtime history inside the database, where it survives restarts. Its Regressed Queries report shows queries whose performance has changed and the plans involved, and a known-good plan can be forced while the cause is fixed. Available since SQL Server 2016, it is only enabled by default for databases created on SQL Server 2022 or later, so check rather than assume.

Storage latency

sys.dm_io_virtual_file_stats records reads, writes and the time spent waiting for them on every data and log file.

SELECT DB_NAME(vfs.database_id) AS database_name,
       mf.physical_name,
       vfs.num_of_reads,
       vfs.io_stall_read_ms / NULLIF(vfs.num_of_reads, 0) AS avg_read_ms,
       vfs.num_of_writes,
       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
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;

These averages cover the whole period since startup and can hide a recent problem, so sample twice and compare. Microsoft’s troubleshooting guidance treats I/O waits consistently above roughly 10 to 15 milliseconds as a bottleneck. If storage is slow, establish whether SQL Server is generating the load (a plan change that scans a large table can saturate good storage) or the storage itself has changed.

Memory grants and memory pressure

Sorts and hash operations need a memory grant before they run. When grants are too large, other queries queue for memory and show RESOURCE_SEMAPHORE waits. sys.dm_exec_query_memory_grants lists current grants: rows where grant_time is NULL are still waiting, and comparing requested_memory_kb with used_memory_kb shows whether estimates are far out. Oversized grants usually come from bad row estimates, which leads back to statistics and plans.

Why adding CPU or memory often doesn’t fix it

Adding hardware is tempting because it can be done without understanding the problem. It often disappoints:

Blocked sessions are waiting for locks, not for CPU. More cores do not make another transaction commit sooner.
A plan that scans millions of rows instead of seeking to a handful is orders of magnitude slower. Faster hardware narrows that gap only slightly.
Memory grant problems come from estimates. A query that asks for far too much memory will ask for far too much on a bigger server.
New memory is not used until max server memory allows it, and on a VM the host has to be able to provide it.
SQL Server is usually licensed per core, so extra vCPUs can bring a significant licence cost for no gain.

Genuine capacity problems do exist, and more memory can be the right answer to sustained heavy reads. That should be a conclusion drawn from evidence, not the first move.

Quick fixes that make things worse

Restarting to “clear the cache”

A restart often appears to work, because the bad plan is discarded and the next compilation happens to choose a better one. It also wipes the wait, query and file statistics you needed, empties the buffer pool so the server runs on a cold cache for a while, and ends in-flight work. Long transactions have to be rolled back during recovery, which can keep the database unavailable for some time unless accelerated database recovery is enabled (SQL Server 2019 and later). And the bad plan tends to return the next time the wrong parameter value is compiled first.

Shrinking databases

Shrinking moves pages from the end of a file into free space nearer the start, which fragments indexes. If the free space was needed, the file grows again, and growth events cost performance while they happen. Shrinking is for recovering space after a large one-off deletion, not a tuning step.

Blanket index rebuilds

Rebuilding every index generates a large volume of transaction log, consumes CPU and I/O, and when run offline holds locks that block users. When a rebuild does seem to help, the reason is usually that it updated statistics with a full scan and caused plans to recompile. Microsoft’s index maintenance guidance makes the same point: updating the relevant statistics often achieves that benefit at a much lower cost.

When to call a specialist

Many slowdowns can be resolved in-house with the steps above. It is worth bringing in a SQL Server specialist when:

the system is business-critical and the slowdown is costing money or customers while you investigate;
the evidence points in several directions at once, or the obvious fix has not held;
the likely remedy involves forcing plans or changing indexes, isolation levels or compatibility levels on production;
you suspect the storage or virtualisation layer and need hard evidence to take to the infrastructure team or hosting provider;
the problem keeps coming back, which usually means the root cause has not been found.

Whoever investigates, the most valuable handover is untouched evidence: when the problem started, what changed, and the output of the queries above captured while the system was slow.

If your SQL Server has slowed down and you would like a clear explanation rather than another restart, a focused performance investigation is a sensible next step. Talk to a SQL Server specialist at SQLCare about what you are seeing, and we will tell you plainly what we would look at first.