What are statistics in SQL Server, and why do they matter?
Quick Answer
Statistics are metadata about the distribution of values in a column or index — a histogram plus density info. The query optimiser uses them to estimate how many rows a predicate will return (cardinality estimation) and therefore choose a plan. Stale or missing statistics lead to bad row estimates and bad plans.
Statistics describe how data is distributed in a column — a histogram of value ranges and their row counts. The optimiser leans on them to guess how many rows a filter will match, and that guess drives the whole plan: seek versus scan, which join type, how much memory to grant. When statistics are stale, the estimates are wrong and you get a bad plan — the tell is a large difference between estimated and actual rows in the plan. The fix is updating statistics, and for big volatile tables I'd have a maintenance job doing that regularly rather than relying on auto-update.
- Created automatically for indexed columns and (with
AUTO_CREATE_STATISTICSon) for columns used in predicates. - Auto-updated when enough rows change — the old threshold was ~20% of the table, which is far too slow for large tables (SQL 2016+ uses a dynamic threshold).
- Symptom of stale stats: a big gap between Estimated and Actual rows in the execution plan.
- Fix:
UPDATE STATISTICS table WITH FULLSCAN, or an index rebuild (which updates them fully). Schedule regular stats maintenance on volatile large tables. - The Cardinality Estimator changed significantly in SQL Server 2014 — legacy vs new CE can produce different plans.