Statistics sampling and skew

In one line: statistics are built from a sample unless you say otherwise, and a histogram has at most 200 steps. A sample gets frequent values right and rare or absent values wrong. And an automatic update quietly goes back to sampling, undoing your FULLSCAN.

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

What you’ll see

  • Overestimates for rare values. A customer with 2 orders was estimated at 9.5 from the default sample.
  • Phantom rows for values that don’t exist. A “no rows” lookup was estimated at 23.6 rows instead of 1.
  • Neighbouring values with very different row counts but the same estimate.
  • Plans that are fine after the nightly UPDATE STATISTICS ... WITH FULLSCAN and bad again by mid-morning.

Why it happens

  • A sample sees few of the rare values, so it underestimates how many distinct values exist and overestimates each one. Frequent values come out within about 1%.
  • 200 steps is the hard limit. A value without its own step is estimated from the average of its step’s range. In the demo, neighbours with 1,900 and 100 rows were both estimated at 957, even after a full scan.
  • Automatic updates use the default sample rate, whatever the last manual update used. 80,000 new rows triggered one, and it sampled 9.56%.

What to do

  • FULLSCAN for statistics on skewed columns where rare and absent values matter.
  • PERSIST_SAMPLE_PERCENT = ON (2016 SP1 CU4+) with it, so automatic updates keep the rate. In the demo, the automatic update then sampled 100%.
  • Filtered statistics for an important range that 200 steps can’t describe.

What doesn’t help

  • A bigger sample against the 200-step limit. Full scan still gave 957 for both neighbours.
  • A nightly FULLSCAN on its own. The first automatic update after it goes back to sampling.

Check your server

-- How each statistic in this database was last sampled, and whether the rate is persisted.
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,
       SampledPct = CAST(100.0 * sp.rows_sampled / NULLIF(sp.rows, 0) AS decimal(5, 2)),
       sp.persisted_sample_percent,
       sp.steps,
       sp.modification_counter
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
ORDER BY sp.rows DESC;

A statistic you update WITH FULLSCAN that shows a low SampledPct and a persisted_sample_percent of 0 has been overwritten by an automatic update.

See it happen

The statistics-sampling demo builds 5,000,000 deliberately skewed rows. It measures estimates under the default sample, under FULLSCAN, and after an automatic update with and without a persisted rate.

Related: the ascending-key problem · correlated predicates · parameter sniffing

Measured on SQL Server 2022 CU25 (16.0.4255.1). The default sample is random, so its numbers move a little between runs.

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