Part 4 — Fast at scale › Chapter 28

Statistics, cardinality and parameter sniffing

Why the same query is fast for one customer and slow for another, and the four things you can do about it.

From the advert

“Ottimizzazione e tuning delle query” — and the class of problem where the query, the indexes and the data are all fine and it is still slow.

The optimiser is guessing, and statistics are the guess

This is why the first thing to read in a plan is the gap between estimated and actual rows — it names the step whose premise was false.

🧒 Simple

Before you set off you decide whether to walk or take the bus, and you decide using how far you think it is. If your idea of the distance is six months out of date, you will make the wrong choice — not because you chose badly, but because you were told the wrong distance.

🎓 Professional

A cost-based optimiser picks a plan by estimating how many rows each step will produce, using statistics: a histogram of value distribution per column plus a density (average duplicates). Every join order, join algorithm and access method decision follows from those estimates. Stale or missing statistics therefore do not make the optimiser slow — they make it confidently wrong, which is much harder to see.

Seeing what the optimiser believes, and telling it to look again.

-- Look at what it BELIEVES about the data
DBCC SHOW_STATISTICS('dbo.orders', 'ix_orders_status');   -- SQL Server: the histogram itself
SELECT * FROM pg_stats WHERE tablename = 'orders';        -- PostgreSQL: per-column statistics

-- Tell it to look again
UPDATE STATISTICS dbo.orders WITH FULLSCAN;               -- SQL Server: read every row, not a sample
ANALYZE orders;                                           -- PostgreSQL: resample this table
ANALYZE TABLE orders;                                     -- MySQL: the same idea again

What statistics are, and how they go wrong

Statistics are a histogram: how many rows fall in each range of values. The optimiser uses them to guess how many rows a predicate will match, and that guess decides seek versus scan, and nested loops versus hash join.
Stale statistics do not make the optimiser slow. They make it confidently wrong — it plans for 10 rows and meets 10 million, and a nested loop that was optimal becomes a catastrophe.
Sampling is the default because reading every row is expensive. WITH FULLSCAN is exact and slow: right after a bulk load, wrong as a nightly habit on a large table.
The classic symptom is an ascending key: rows added today are beyond the histogram's last bucket, so “yesterday's orders” is estimated at one row. Recognising that symptom is most of what an interviewer is checking.

⚠ Trap — auto-update fires later than you think

SQL Server's classic threshold was 20% of the rows plus 500. On a table with 100 million rows that is 20 million modifications before statistics refresh themselves — so a table can be badly out of date for weeks while looking healthy. (2016+ uses a square-root-based threshold that is much better, but only under a recent compatibility level.) On a large, actively-loaded table, scheduled statistics maintenance is not optional.

Parameter sniffing

🧒 Simple

The first customer through the door orders one coffee, so you set up a small tray. Then a coach party arrives and you carry two hundred coffees one at a time on the small tray — because you planned for the first person you saw and never reconsidered.

🎓 Professional

On first execution, the engine compiles a plan using the actual parameter values of that call — it “sniffs” them — and caches that plan for all subsequent calls. That is usually correct and usually free. It goes wrong when the data is skewed: a plan compiled for a customer with 3 orders (nested loops, index seek, lookup) is catastrophic when reused for a customer with 400 000, and vice versa. The symptom is unmistakable: the same stored procedure is fast for one input and slow for another, and restarting the service "fixes" it — because it cleared the cache and re-sniffed.

The four fixes, and when each is right

Fix	Does	Right when	Costs
`OPTION (RECOMPILE)`	New plan every execution, using the real values	Infrequent, expensive, highly variable queries — and reporting screens with optional parameters	Compilation CPU on every call
`OPTIMIZE FOR (@p = 'x')`	Always plan as if the parameter were x	One value dominates the workload	You have hard-coded an assumption that will age
`OPTIMIZE FOR UNKNOWN`	Use average density, ignore the actual value	You want one mediocre plan rather than a coin flip	Never optimal for anybody
Split the procedure	Separate code paths per shape, each with its own cached plan	There are genuinely two workloads	More code, and it is honest about why

The local-variable trick, and why not to use it

Assigning a parameter to a local variable inside a procedure defeats sniffing, because the optimiser cannot see a local's value at compile time — so it uses average density. It is equivalent to OPTIMIZE FOR UNKNOWN, but invisible: the next person to read the procedure has no idea it is a performance decision and will "tidy" it away. If you want that behaviour, ask for it by name in a hint.

Where estimation fails even with perfect statistics

Correlated columns. The optimiser assumes independence, so WHERE city = 'Modena' AND region = 'Emilia-Romagna' is estimated as p(city) × p(region) — a big underestimate, because every Modena row is already Emilia-Romagna. PostgreSQL can be told: CREATE STATISTICS … (dependencies) ON city, region FROM customers. SQL Server needs a multi-column statistic or a rewrite.
Table variables in T-SQL historically estimate one row, whatever they contain. That single fact explains an enormous number of slow procedures; a temp table has real statistics and is usually the fix.
Multi-statement table-valued functions estimate a fixed guess (1 row, or 100 in newer versions) regardless of reality. Inline table-valued functions do not have this problem — they are expanded into the query.
Scalar UDFs in a predicate are a black box: no estimate, no parallelism, and pre-2019 SQL Server executed them once per row.
Expressions the optimiser cannot see through: a parameter compared to a computed value, a LIKE with a variable pattern, dynamic date arithmetic.

✔ The one-line diagnostic

“The same query is fast for one input and slow for another” is parameter sniffing until proven otherwise. “It got slow after the nightly load” is stale statistics until proven otherwise. Being able to name the right suspect from the symptom is most of what the interviewer is checking.

The plan cache

The plan cache: which statements cost the most in total.

-- SQL Server: sort by TOTAL cost, never by average.
SELECT TOP 20                                               -- the twenty worst offenders
       qs.execution_count,                                  -- how often it ran
       qs.total_worker_time / 1000 AS cpu_ms,               -- microseconds → milliseconds
       qs.total_logical_reads,                              -- 8 KB pages touched, in total
       qs.total_elapsed_time / qs.execution_count / 1000 AS avg_ms,  -- mean duration, ms
       SUBSTRING(t.text, 1, 200) AS sql                     -- the first 200 characters
FROM sys.dm_exec_query_stats qs                             -- one row per cached plan
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) t           -- the text behind the handle
ORDER BY qs.total_worker_time DESC;                         -- the biggest total wins

-- PostgreSQL, with pg_stat_statements enabled:
SELECT calls, total_exec_time, mean_exec_time, rows,        -- the same four questions…
       left(query, 120)                                     -- …and the statement, truncated
FROM pg_stat_statements                                     -- one row per normalised statement
ORDER BY total_exec_time DESC LIMIT 20;                     -- again: total, not mean

Why total and not average

The query that runs ten thousand times at 8 ms costs eighty seconds of server time. The nightly report that takes four seconds costs four. Sorting by average finds the second and misses the first, and the first is what your users are waiting for.
total_worker_time is CPU; total_elapsed_time is wall clock. A big gap between them means waiting — for locks, or for disk — and that is a different investigation (chapter 42).
/ 1000 twice in one expression: these columns are microseconds, and integer division truncates. For a sub-millisecond statement that reads as 0 — accurate enough for ranking, misleading if you quote it.
Both views are cumulative since the last restart or reset. Note the uptime before drawing conclusions.

⚠ Trap — sorting by average duration hides the real problem

A query taking 4 ms and running two million times a day costs far more than one taking 9 seconds and running twice. Sort by total. This is also the only way the N+1 problem is ever visible in these views.

The optimiser chooses by estimating rows. Statistics are the estimate.
Stale statistics do not make it slow — they make it confidently wrong.
SQL Server's classic auto-update threshold is 20% of rows plus 500, which is far too late on a large table.
Parameter sniffing caches a plan built for the first values seen and reuses it for all.
“Fast for one customer, slow for another” means sniffing. “Slow since the nightly load” means statistics.
RECOMPILE for variable expensive queries; OPTIMIZE FOR when one shape dominates; splitting the procedure when there are genuinely two workloads.
The local-variable trick works and is invisible. Use a hint so the next reader knows it is deliberate.
The optimiser assumes columns are independent, so correlated predicates are underestimated.
Table variables and multi-statement TVFs estimate a fixed guess. Temp tables have real statistics.
Sort the plan cache by total time, never average.

Interview questions this chapter answers

What is parameter sniffing?

The optimiser compiles a plan using the parameter values of the first execution and caches it. That is normally desirable; with skewed data it means a plan built for a tiny input is reused for a huge one. The tell is a procedure that is fast for one argument and slow for another, and that "fixes itself" after a restart.

A query was fast yesterday and is slow today, and nothing was deployed. Why?

Most likely stale statistics after a data load, or a recompiled plan that got sniffed with a different parameter. I would compare estimated versus actual rows in the plan; a large gap points at statistics, and a plan that changed shape points at sniffing.

Why might a table variable make a procedure slow?

It has no meaningful statistics — historically an estimate of exactly one row, whatever it holds. So the optimiser plans the rest of the query as if it were joining to one row, typically choosing nested loops, and then executes that against fifty thousand. A temp table has real statistics and usually fixes it outright.