SQL Server Statistics Explained: Why Your Query Plans Go Wrong

Originally published at SQL & Cloud Lab.

This post was drafted with AI assistance.

When a query suddenly picks a terrible plan, the root cause is very often statistics. SQL Server's optimizer does not look at your data when it builds a plan; it looks at statistics about your data. If those are missing or out of date, it guesses wrong, and a wrong guess can mean a scan instead of a seek, the wrong join type or a memory spill.

What statistics are

A statistics object describes how values are distributed in one or more columns. SQL Server creates one automatically for every index, and, with AUTO_CREATE_STATISTICS on, for columns you filter or join on. The optimizer uses them to estimate how many rows each step will return, and those estimates drive almost every plan choice.

How to read a statistics object

DBCC SHOW_STATISTICS ('dbo.PayrollLine', 'IX_PayrollLine_Run_PayCode'); 

The output has three parts:

• Header: when the statistics were last updated, how many rows the table had, and how many rows were sampled.
• Density vector: how unique the column combinations are, used when the actual value is unknown.
• Histogram: up to 200 steps showing, for ranges of values in the first column, how many rows match. This is what lets SQL Server know that Status = 'Closed' matches 2 million rows but Status = 'Error' matches 12.

When statistics update automatically

With AUTO_UPDATE_STATISTICS on (the default), statistics are refreshed when enough rows have changed and a query needs them. On modern versions (compatibility level 130 and above) the threshold is dynamic, so large tables are refreshed after a smaller percentage of changes than in old versions. Even so, on a table with hundreds of millions of rows, a lot can change before an automatic update kicks in.

Automatic updates also use a sample of rows by default, which can miss skewed values in very large tables.

Check how stale your statistics are

SELECT OBJECT_NAME(s.object_id) AS table_name, s.name AS stats_name, sp.last_updated, sp.rows, sp.rows_sampled, sp.modification_counter FROM sys.stats s CROSS APPLY sys.dm_db_stats_properties(s.object_id, s.stats_id) sp WHERE OBJECTPROPERTY(s.object_id, 'IsUserTable') = 1 ORDER BY sp.modification_counter DESC; 

A high modification_counter means many rows have changed since the last update. A low rows_sampled compared with rows means the statistics are based on a small sample.

Update statistics manually when it matters

-- One table, default sample UPDATE STATISTICS dbo.PayrollLine; -- One statistics object, reading every row (most accurate, most expensive) UPDATE STATISTICS dbo.PayrollLine IX_PayrollLine_Run_PayCode WITH FULLSCAN; -- All statistics in the database that have had modifications EXEC sp_updatestats; 

Use FULLSCAN for important, skewed columns on large tables; it costs more I/O but gives the most accurate histogram. After a big data load, updating statistics on the affected tables before users run queries prevents a wave of bad plans.

The ascending key problem

Columns that always grow, such as identity values or CreatedAt dates, cause a classic issue: new rows are added beyond the highest value in the histogram, and until statistics are updated, SQL Server may estimate that very few rows exist there. Queries on "today's" or "the latest" data then get plans built for tiny row counts. Newer cardinality estimator versions handle this better, but frequent statistics updates on these tables are still the most reliable fix.

Index rebuilds vs statistics updates

• Rebuilding an index also updates that index's statistics with a full scan.
• It does not update auto-created column statistics.
• Reorganizing an index does not update statistics at all.

So a maintenance job that only reorganizes or rebuilds indexes can leave important column statistics stale. Include a statistics step in your maintenance.

Practical routine

• Keep AUTO_CREATE_STATISTICS and AUTO_UPDATE_STATISTICS on.
• Schedule a regular statistics update, such as nightly or weekly, for busy tables.
• Update statistics after large loads, purges or migrations.
• When a plan goes bad, compare estimated and actual rows; if they differ a lot, check the statistics on those columns first.

Related reading:

• SQL Server Parameter Sniffing: What It Is and How to Fix It
• How to Read a SQL Server Execution Plan
• SQL Server Index Tuning for Beginners

More SQL Server guides on my blog, SQL & Cloud Lab.