The index existed. The seek was in the plan. And the query still took forty seconds, because the plan said one row and the table said fourteen million. The on-call fix that morning was a brand-new index suggested by the missing-index DMV, which changed nothing, because the problem was never the index. It was the estimate. The optimizer is exactly as smart as the statistics you feed it, and on that system the statistics had not been updated in eleven days.
Statistics are the least glamorous objects in a database and the ones with the most leverage over plan quality. Every seek-versus-scan choice, every join order, every memory grant starts from a row estimate, and every row estimate starts from a histogram. Here is how I keep that fuel clean on SQL Server 2016 through 2022, and how I triage it when a plan goes sideways for no visible reason.
What does the optimizer actually read from a statistics object?
Three things: a header with row counts and the last update time, a density vector covering column combinations, and a histogram of at most 200 steps over the leading column. The histogram is the part that matters for WHERE-clause estimates. Each step covers a key range bounded by RANGE_HI_KEY and records the rows inside the range, the rows equal to the boundary value, the distinct values in the range, and the average rows per distinct value. Equality on a boundary value reads the EQ_ROWS column. Equality between boundaries reads the step average. A range predicate sums the steps it overlaps. That is the whole mechanism, and most estimates you will ever argue about come out of those 200 steps — the rest come out of the density vector, table cardinality, constraints, and the fixed guesses the CE falls back to when no histogram applies.
You can read precisely what the optimizer sees with DBCC SHOW_STATISTICS against the table and statistics name, with the HISTOGRAM option. Two hundred steps is a hard limit on a table of any size, which is why a billion-row table's histogram is a sketch and not a photograph: each step averages across an enormous range, and skew inside a single step is invisible to it. When someone tells me the estimate is wrong, my first question is which step their value landed in, not whether the statistics object exists. It always exists. What it contains is the question.
When does auto-update fire, and why is the 20% rule a trap?
On compatibility levels below 130, a statistics object auto-updates after roughly 500 rows plus 20 percent of the table have changed since the last update. On a ten-thousand-row table that trigger is generous. On a hundred-million-row table it means twenty million modifications can land while the histogram goes stale, and stale is a polite word for what happens to estimates in that window. Trace flag 2371 replaced the fixed percentage with a sublinear threshold, and from SQL Server 2016 at compatibility level 130 that dynamic threshold is simply the default: the trigger becomes the smaller of the old 500-plus-20-percent rule and the square root of one thousand times the row count — a crossover that happens just under twenty thousand rows — so our hundred-million-row table re-collects after about 316,000 changes instead of twenty million.
The catch is that auto-update samples. Sampling is the right default, a full scan of a hundred million rows purely to refresh a histogram would be its own incident, but a sample can misread skewed data badly, and it knows nothing whatsoever about rows inserted since it ran. AUTO_UPDATE_STATISTICS_ASYNC, which I switch on for busy OLTP databases, takes the update off the critical path: the triggering query compiles against the old statistics and the fresh ones benefit the next compilation, trading one stale plan for no blocking. What it does not fix is the threshold itself. For that you schedule your own updates, and every large table I have owned for long eventually gets one.
Sampled versus FULLSCAN: when does sampling lie?
Sampling lies exactly when skew lives at a scale smaller than the sample. A default sample reads a small percentage of pages; a hot value concentrated on a handful of pages, a status code worth millions of rows inside one partition, can be missed or averaged into nothing, and the estimate comes back as one row for a predicate that matches millions. My standing rule: any column that has caused a bad plan twice earns a FULLSCAN update, and then a scheduled FULLSCAN update, because one manual fix without a schedule is just a delay before the next incident.
UPDATE STATISTICS Sales.Orders IX_Orders_Status
WITH FULLSCAN, PERSIST_SAMPLE_PERCENT = ON;PERSIST_SAMPLE_PERCENT matters because without it the next auto-update quietly reverts to the default sample and overwrites your careful work. The option arrived in SQL Server 2016 SP1 CU4 and 2017 CU1, and sys.dm_db_stats_properties exposes the persisted value in its persisted_sample_percent column, so you can audit the setting instead of trusting a wiki page. Audit it on your top twenty tables. Half of them will not be what you think they are.
Why does the ascending-key problem keep coming back?
Because every busy table has a column that only grows: order dates, identity keys, created-at timestamps. The histogram's top step is frozen at whatever the maximum was the last time statistics updated, and rows inserted after that point are invisible to it. Under the legacy cardinality estimator, a predicate beyond the last RANGE_HI_KEY estimates one row. Under the 2014-and-later CE the estimate comes from the density vector, the average rows per distinct value, which is usually less wrong but still knows nothing about a million fresh rows. Either way the estimate drifts low, and low estimates choose nested loops and tiny memory grants for what is actually a fat, growing range. That is how a report that scans last week's orders gets a plan shaped for twelve rows.
The fixes are boring and they work. Update statistics on the ascending column on a schedule measured in hours, not days. Trace flags 2389 and 2390 brand a statistics object as ascending and change the legacy CE's out-of-range behavior, though at compatibility level 120 and above the new CE's density-based estimate has made them mostly unnecessary. And a filtered statistic, CREATE STATISTICS with a WHERE clause over the trailing window of recent rows, gives the histogram fine steps exactly where the queries live, at a fraction of the cost of a full-table refresh. Same engine, sharper lens.
How do I triage statistics before touching anything?
sys.dm_db_stats_properties is the triage view. Per statistics object it reports the last update time, the table row count, how many rows were sampled, the modification counter since the last update, the histogram step count, and the persisted sample setting. The first query I run when a plan surprises me:
SELECT s.name AS stats_name,
sp.last_updated,
sp.rows,
sp.rows_sampled,
sp.steps,
sp.modification_counter,
sp.persisted_sample_percent
FROM sys.stats AS s
CROSS APPLY sys.dm_db_stats_properties(s.object_id, s.stats_id) AS sp
WHERE s.object_id = OBJECT_ID('Sales.Orders')
ORDER BY sp.modification_counter DESC;Read modification_counter against rows. A counter at fifteen percent of a huge table with a last_updated from last week is the smoking gun under the old 20-percent rule, and an even louder one under the dynamic threshold: the square-root formula fires far earlier on a big table, so a counter that far past the trigger means auto-update is off or failing, not that the threshold is generous. That inversion is precisely the trap people fall into after upgrading and assuming the problem went away. Compare rows_sampled against rows as well: anything sampling under ten percent on a skewed column gets my attention. And count the auto-created statistics, the _WA_Sys_ names. They are single-column and start life sampled — a later manual FULLSCAN update can change that, so read rows_sampled before assuming — and they multiply quietly. A table with forty of them is a table where nobody ever designed statistics on purpose.
When is the CE itself the problem, and what are the escape hatches?
Sometimes the statistics are fresh, the histogram is representative, and the estimate is still wrong, because the 2014 cardinality estimator made different modeling assumptions than the legacy one around join containment and multi-predicate correlation. The rework fixed real pathologies and introduced new ones, and Microsoft shipped per-query hatches for the new ones: OPTION (USE HINT('FORCE_LEGACY_CARDINALITY_ESTIMATION')) drops a single query back to the legacy model, and the LEGACY_CARDINALITY_ESTIMATION database-scoped configuration does it wholesale. My rule is per-query only. A global CE downgrade to fix one report punishes every other query in the database, and I have watched exactly that happen in production.
Which brings me back to the reflex that opened this piece. When a plan is bad, the missing-index DMV and the tuning advisor will happily sell you an index. Check the estimate first: open the actual execution plan, compare Estimated Number of Rows against Actual Number of Rows on the thick operators, and when those numbers disagree by two orders of magnitude you are holding a statistics problem. The tenth redundant index the DMV wanted would have fixed nothing. Query Store does not capture per-operator actuals — it stores the estimated plan plus runtime aggregates, which is enough to shortlist the queries whose cost and row counts moved — so the thick-operator comparison itself still comes from an actual execution plan, and the regression workflow is written up in my notes on hunting regressions with Query Store. Estimates first, indexes second.
Watching estimates drift with MonPG when SQL Server support ships
The telemetry I want here is unglamorous: per-table modification counters against the auto-update threshold, last-updated age on critical statistics, and estimated-versus-actual divergence per query over time, because estimate drift is visible in the counters weeks before it becomes an incident. MonPG monitors PostgreSQL in production today; SQL Server support is on the roadmap and in active development, and statistics freshness is exactly the kind of boring, decisive counter it should graph. Honest status lives on the SQL Server monitoring (coming soon) page. Until it ships, dm_db_stats_properties and a calendar reminder are the entire monitoring stack.