Statistics: Staleness and Updates

Everything the optimizer believes about your data comes from statistics. A plan built on stale statistics is a plan built for a table that no longer exists.

Module 3 covered statistics as a maintenance priority. This lesson is about their role in plan stability — because unstable plans and sniffing problems are often really statistics problems wearing a disguise.

What a statistic contains

Three parts:

The header — total rows, rows sampled, last updated timestamp.
The density vector — average selectivity of each column prefix. This is what OPTIMIZE FOR UNKNOWN and local variables use.
The histogram — up to 200 steps describing the distribution of the leading column, each step recording a range boundary, the row count at that exact boundary, and the count within the range.

Look at one directly:

DBCC SHOW_STATISTICS ('dbo.Orders', 'IX_Orders_OrderDate');   -- placeholder table + index

Three result sets appear. Read the header's Rows against the table's real row count, Rows Sampled to see how much was examined, and Updated for staleness. Then look at the histogram: if your report's date range falls beyond the last RANGE_HI_KEY, the optimizer is estimating rows for a region it has never seen.

Or query it as a set, which is easier to filter:

SELECT  hist.step_number,
        hist.range_high_key,
        hist.range_rows,
        hist.equal_rows,
        hist.distinct_range_rows,
        hist.average_range_rows
FROM sys.stats AS s
CROSS APPLY sys.dm_db_stats_histogram(s.object_id, s.stats_id) AS hist
WHERE s.object_id = OBJECT_ID('dbo.Orders')
  AND s.name = 'IX_Orders_OrderDate'
ORDER BY hist.step_number;

The 200-step limit is the practical constraint

Two hundred steps, regardless of table size. On a table with five years of daily data — roughly 1,800 distinct dates — each step covers about nine days. On a fifty-million-row fact table, each step describes a quarter of a million rows as a single average.

This is why estimates for recent data are so often wrong on a large table: the newest rows fall in the last step or beyond the last boundary entirely, and the optimizer averages across a wide bucket or extrapolates.

The mitigations:

Filtered statistics on the hot slice (Module 3 Lesson 5) — 200 steps describing only the current year is far finer resolution than 200 steps describing five years.
Partitioning with incremental statistics, so each partition gets its own histogram.
More frequent updates on the tables that grow, rather than everything on a schedule.

The ascending key situation

A specific and common case worth naming. A fact table's date column always grows at the high end. Rows inserted since the last statistics update fall above the highest histogram boundary, and the optimizer historically estimated 1 row for values in that region.

A report filtering "since yesterday" on a table that gained 400,000 rows today therefore estimates 1 row, chooses nested loops, and performs 400,000 lookups. This is the classic "the report is fast most of the month and terrible right after month-end loading" pattern.

Trace flags 2389/2390 addressed this historically; the modern cardinality estimator (compatibility level 120+) handles ascending keys with a better default assumption. If you are stuck at a low compatibility level with a heavily-inserted fact table, this is a strong argument for either raising the level or scheduling more frequent statistics updates on that table specifically.

Auto-update, and its stall

Recapping the thresholds from Module 3, because they matter to plan stability:

Compatibility 120 and below: 500 rows + 20% of the table. On 50 million rows, 10 million modifications before anything updates.
Compatibility 130+: roughly SQRT(1000 × rows). On 50 million rows, about 224,000 modifications. Dramatically more responsive.

And the synchronous stall: with AUTO_UPDATE_STATISTICS_ASYNC off (the default), the query that crosses the threshold waits for the statistics update before it compiles. On a large table with FULLSCAN-worthy volume, that is a query that inexplicably took 40 seconds once and has been fast ever since. Async removes the stall:

ALTER DATABASE [YourDatabase] SET AUTO_UPDATE_STATISTICS_ASYNC ON;

The trade: the triggering query compiles against the old statistics one more time.

Updating deliberately

-- One index's statistics, full scan
UPDATE STATISTICS dbo.Orders IX_Orders_OrderDate WITH FULLSCAN;

-- All statistics on a table
UPDATE STATISTICS dbo.Orders WITH FULLSCAN;

-- Sampled, for very large tables where FULLSCAN is too expensive
UPDATE STATISTICS dbo.Orders WITH SAMPLE 30 PERCENT;

-- SQL Server 2016 SP1+: pin a persistent sample rate so auto-updates keep using it
UPDATE STATISTICS dbo.Orders WITH SAMPLE 30 PERCENT, PERSIST_SAMPLE_PERCENT = ON;

PERSIST_SAMPLE_PERCENT is genuinely useful and underused: without it, an automatic update after your careful FULLSCAN reverts to the default low sample rate and undoes your work. With it, subsequent auto-updates honour the rate you chose.

Finding what needs attention

SELECT  OBJECT_SCHEMA_NAME(s.object_id) AS schema_name,
        OBJECT_NAME(s.object_id)        AS table_name,
        s.name                          AS stats_name,
        sp.last_updated,
        DATEDIFF(DAY, sp.last_updated, SYSDATETIME()) AS days_old,
        sp.rows,
        sp.rows_sampled,
        CAST(100.0 * sp.rows_sampled / NULLIF(sp.rows,0) AS decimal(5,2)) AS pct_sampled,
        sp.modification_counter,
        sp.steps
FROM sys.stats AS s
CROSS APPLY sys.dm_db_stats_properties(s.object_id, s.stats_id) AS sp
WHERE OBJECTPROPERTY(s.object_id,'IsUserTable') = 1
  AND sp.rows > 100000
  AND (sp.modification_counter > sp.rows * 0.10 OR sp.last_updated < DATEADD(DAY, -14, SYSDATETIME()))
ORDER BY sp.modification_counter DESC;

That gives you a short, actionable list: large tables whose statistics are either 10%+ modified or two weeks stale.

Watch out for one measurement artefact

Updating statistics invalidates the plans that depend on them, so the next execution recompiles. If the query gets faster, you cannot tell whether the new statistics or the fresh compile did it. When that distinction matters, test them separately: run once with OPTION (RECOMPILE) on the old statistics first, then update and re-measure.

The short version

The histogram is capped at 200 steps regardless of table size, so on a big fact table each step covers a wide range and estimates for recent data are frequently wrong — especially on an ascending date key, where new rows sit above the last boundary. Check dm_db_stats_properties for staleness and sample rate, use PERSIST_SAMPLE_PERCENT so auto-updates do not undo a full scan, and consider filtered statistics on the hot slice when the whole-table histogram is too coarse.