Find out what it is waiting on before you change anything

Insights

Most SQL Server performance work goes wrong before anyone touches a query, in the diagnosis. This is the order we work in: what the server is waiting on, why the row estimates are wrong, why parameter sniffing looks like a haunting, and why the nightly index rebuild is probably not the thing helping you. Opinions are marked as opinions, and where the honest answer is that it depends on your data, it says so.

CPU utilisation is nearly useless as a starting point. sys.dm_os_wait_stats will tell you what the instance spent its time waiting for, but the counters are cumulative since startup, so by default you get a lifetime average that ranks a benign background wait first while the incident you care about lasted four minutes yesterday. Take two snapshots a few minutes apart during the problem and subtract them. That delta is the only version of this data worth reasoning about.

None of this is a verdict. Wait statistics eliminate whole categories, which is most of their value. Knowing you have a locking problem rather than an I/O problem saves a week of index work that was never going to help.

Get an actual execution plan, not an estimated one, and compare estimated rows with actual rows at every operator. Where those two differ by orders of magnitude you have found the bug, because everything downstream was chosen on the strength of it: a nested loop because the optimiser expected eight rows and met eight hundred thousand, or a memory grant sized for a small sort that then spills to tempdb.

Estimates go wrong for a short list of recurring reasons. Statistics are stale, or were sampled too thinly. The leading edge of an ascending key sits outside the histogram, so the newest rows, which is exactly what every dashboard asks for, estimate at almost nothing. The histogram has a fixed step limit, so heavy skew across many distinct values gets described very coarsely. A scalar function in a predicate hides its cost. An expression wrapped around a column discards the histogram entirely.

Cardinality estimation is a model, and its assumptions are mostly about how predicates combine: filter on three correlated columns and the independence assumption underestimates, sometimes badly. You are locating where your data violates the model, then either giving the optimiser better information or removing its need to guess.

A procedure is compiled for the parameter values it happens to see first, and that plan is cached and reused. With skewed data, one parameter shape deserves a seek and a loop join while another deserves a scan and a hash. Whichever compiled first wins, and the other one suffers.

This is why it presents as inexplicable. It changes after a restart, a failover, a statistics update, anything that evicts the plan. Nothing was deployed, it got slow, later it got fast again. That is the signature.

Every remedy costs something, and choosing between them is a judgement about your data rather than a best practice. OPTION (RECOMPILE) gets the right plan every time and pays a compilation on every execution, which is fine for a nightly report and ruinous at thousands of calls a minute. OPTIMIZE FOR UNKNOWN gives everybody the average plan: predictable, mediocre, and frequently the right trade. Forcing a plan in Query Store stabilises things this afternoon and freezes the optimiser's hands until somebody finds it years later.

There is no honest general answer here. It depends on how skewed the data is, and on whether the workload would rather be predictably mediocre or occasionally awful.

Key column order decides whether a seek is possible at all: equality predicates first, then the range predicate.

SARGability decides whether the key gets used. Any function or conversion on the column side of a predicate makes it unusable for seeking. The common case in the wild is not a developer wrapping a column in a function but the type mismatch nobody wrote: a string parameter arriving from the application as nvarchar against a varchar column. Type precedence converts the column rather than the parameter, and you get a scan. Search the plan's predicate text for CONVERT_IMPLICIT. The plan will still show seeks elsewhere, which is enough to stop most people reading.

Covering decides what the seek costs. An index that locates rows but not columns pays a key lookup per row, and past a few thousand rows that dominates the query. INCLUDE fixes it, and buys you a wider index and more work on every write.

The missing index DMVs are real signal and poor recommendations: no meaningful column-order intelligence, no account of write cost, and no way to tell you the query itself is wrong. Treat them as evidence that a query struggled, not as a script to run.

Logical fragmentation matters far less on flash than a nightly rebuild implies. What most teams get from the rebuild is the statistics update that comes with it, which is why switching to reorganise to be gentler sometimes makes plans worse: rebuild updates statistics, reorganise does not. Low page density is a separate and genuine problem.

Under the default isolation level readers take shared locks, and a long write transaction stops them. The symptom is an application that appears frozen while the server is nearly idle. There is nothing to tune in the blocked query. The problem is the transaction holding the lock, usually because something slow or interactive is happening inside a transaction that should have been short.

Deadlocks are a different event. SQL Server captures deadlock graphs in a system health trace by default, but it is a ring buffer and it wraps. An empty buffer is not evidence that there were no deadlocks.

Lock escalation deserves its own warning, because it turns routine maintenance into an outage. Once a single statement accumulates enough locks on one object, the engine escalates to locking the whole table, so a bulk delete written as one statement blocks the application for its duration. Batch it into small committed chunks and the problem goes away.

Snapshot and read committed snapshot isolation move readers onto row versions instead of locks. Often the right answer, and not a tuning knob: it adds per-row versioning overhead, puts sustained load on tempdb, and changes what queries see. Whether your application tolerates that cannot be answered from the database side.

tempdb allocation contention is the plumbing problem that most looks like a storage problem. Concurrent object creation contends on the allocation bitmap pages at the front of each data file and surfaces as page latch waits, not I/O waits. The fix is several equally sized data files plus the allocation behaviour old trace flags used to enable, which became the default years ago. Faster disks do nothing for it.

For real I/O, sys.dm_io_virtual_file_stats reports read and write stalls per file, which separates two very different claims: this storage is slow, and this plan is reading gigabytes of pages to answer a question about a dozen rows. It is nearly always the second.

Two defaults are worth revisiting, and this is an opinion rather than a rule. Cost threshold for parallelism still defaults to 5, a figure chosen for hardware from another era, so trivial queries go parallel on modern machines. An unbounded degree of parallelism lets one query take the whole server. Setting both deliberately usually helps, and neither rescues a plan that reads the wrong number of pages. Parallelism only burns more cores doing the wrong thing.

The plan cache is a window, not a record. It empties on restart, under memory pressure and on recompilation, so "there is nothing bad in the plan cache" is evidence of nothing. Query Store is the record, and turning it on before you need it is the highest-value item in this article.

Do not tune on averages. The mean duration of a procedure hides the one execution in a few hundred that got the bad sniffed plan, and that is the one people complain about. Look at the distribution.

Change one thing at a time and measure both sides the same way. The second run of a query is faster because its pages are already in the buffer pool, and that is not your fix. Test against data shaped like production too: ten thousand rows in the important table will choose different plans, and a cardinality problem cannot reproduce in data with no skew.

In order: turn on Query Store. Take a wait statistics delta during the bad window rather than since startup. Pull the actual plan for the worst query and compare estimated with actual rows at every operator. Fix the estimate, then the index, then the query shape, one change at a time, measured the same way twice. Leave hardware last, because the cheapest speedup available is almost always the pages the query never needed to read. And the honest answer to how much faster it will be is that nobody knows before it is measured on your data and your hardware. A confident percentage offered before anyone has read your plans is a guess in a suit.

Talk to us about this