The ascending-key problem

In one line: on a column that only grows (a load date, an identity, an OrderDate), every new row lands past the end of the statistics histogram. Queries about recent data get a guess, and the automatic update that should fix it is waiting for a threshold a normal week never reaches.

Run it yourself: the scripts that reproduce everything on this page are in sqlserver-demos/ascending-key on GitHub.

What you’ll see

  • The morning report on “since the last load” or “this week” is slow, and it’s fine again after statistics are updated.
  • Estimates of 1 row for thousands of new rows (the legacy cardinality estimator), or a fraction of them (the new estimator: 6,300 estimated against 21,000 actual in the demo).
  • Seeks with thousands of lookups, serial plans and tiny memory grants where a scan would have been cheaper. That was 2.7× the reads in the demo.

Why it happens

A histogram knows nothing past its last step. Even a full-scan, perfectly healthy histogram built yesterday doesn’t know today exists.

Automatic updates fire when a query compiles and the modification counter has passed a threshold. At compatibility level 130+, that’s √(1000 × rows): 31,623 changes for a million rows, 316,228 for 100 million. A week of loads can stay under it for a long time, and every one of those days has the blind spot.

What to do

  • Update the ascending column’s statistics after each load. A targeted UPDATE STATISTICS is cheap, and even the default sample (about 8% of rows in the demo) put the new week into the histogram. Tie it to the load schedule, not the index maintenance schedule.
  • For one query you can’t schedule around: OPTION (USE HINT ('ENABLE_HIST_AMENDMENT_FOR_ASC_KEYS')) extends the histogram to the column’s current maximum at compile time. It needs an index that leads on the column.

What doesn’t help

  • Rebuilding the clustered index. It refreshes the clustered index’s own statistics. The query needed the statistics on the OrderDate index, which stayed stale. REORGANIZE never refreshes statistics at all.
  • Waiting for auto-update. It’s driven by a modification threshold, not a calendar.
  • “We rebuilt the index and it got fast.” That’s very often a statistics refresh in disguise, and only when the rebuild happened to hit the right index.

Check your server

-- Statistics in this database, with changes since the last update
-- and how far each is from its automatic-update threshold (compat level 130+).
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,
       sp.rows,
       sp.modification_counter,
       AutoUpdateThreshold = CAST(SQRT(1000.0 * sp.rows) AS bigint)
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.modification_counter > 0
ORDER BY sp.modification_counter * 1.0 / NULLIF(sp.rows, 0) DESC;

Look for date and identity columns with a large modification_counter that’s still below the threshold.

See it happen

The ascending-key demo loads one busy week past a full-scan histogram. It compares the legacy and new estimators, finds the exact auto-update threshold, prices the bad plan, and tests which maintenance operations actually fix it.

Related: statistics sampling and skew · key lookups and the tipping point · index fragmentation

Measured on SQL Server 2022 CU25 (16.0.4255.1), 8 schedulers, compatibility level 160. Your numbers will differ; the behaviour shouldn’t.

Part of SQL Server Demos: short explanations of SQL Server problems, each backed by a runnable demo in MarlonRibunal/sqlserver-demos on GitHub. Written with the help of AI; every number comes from running the demo on SQL Server. How this was made.

Verified by MonsterInsights