Updating Statistics in SQL Server and Why It Matters

"It ran in two seconds yesterday and takes twenty minutes today, and nobody deployed anything." I hear that a lot, and when I open the plan it says 1 estimated row against 4 million actual. Usually the statistics no longer describe the data. The optimizer picks joins, memory grants and indexes purely from estimates, and estimates come from statistics. Keep them fresh and a lot of "the server is slow" tickets just don't happen.

Here's what auto update really does, how I find stale statistics, and how I schedule updates on SQL Server 2012 through 2017.

When auto update kicks in

With AUTO_UPDATE_STATISTICS on (the default), SQL Server marks a statistics object stale after a number of modifications and refreshes it the next time a query needs it. The classic threshold is 500 rows plus 20 percent of the table. On a 10 million row table that means 2 million rows must change before anything happens, which is why big tables suffer most.

Trace flag 2371 (SQL Server 2008 R2 SP1 onward) replaces the fixed 20 percent with a sliding scale that gets smaller as tables grow. In SQL Server 2016 that behaviour is on by default for databases at compatibility level 130 or higher, so you only need the flag on older versions or lower compatibility levels.

Check the settings:

SELECT name, is_auto_update_stats_on, is_auto_update_stats_async_on, is_auto_create_stats_on, compatibility_level
FROM sys.databases WHERE database_id > 4;

I leave auto update on everywhere and turn on the async variant only for OLTP databases where a query occasionally stalling for a stats update is worse than running with slightly old numbers:

ALTER DATABASE Sales SET AUTO_UPDATE_STATISTICS_ASYNC ON;

Finding stale statistics

sys.dm_db_stats_properties (SQL Server 2008 R2 SP2 / 2012 SP1 and later) shows the last update time and the modification counter since then:

SELECT OBJECT_SCHEMA_NAME(s.object_id) + '.' + OBJECT_NAME(s.object_id) AS table_name,
       s.name AS stats_name,
       sp.last_updated,
       sp.rows,
       sp.rows_sampled,
       sp.modification_counter,
       CAST(100.0 * sp.modification_counter / NULLIF(sp.rows, 0) AS decimal(5,1)) AS pct_modified
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
  AND sp.rows > 1000
ORDER BY sp.modification_counter DESC;

Two things to look for: a large modification_counter relative to rows, and rows_sampled much lower than rows. Auto update samples a small fraction of a large table, which is fine for evenly distributed data and poor for skewed data.

Updating by hand

For a single table or statistic:

UPDATE STATISTICS dbo.Orders WITH FULLSCAN;
UPDATE STATISTICS dbo.Orders IX_Orders_CustomerID WITH SAMPLE 50 PERCENT;

FULLSCAN reads the whole table and gives the most accurate histogram; on a very large table it can take minutes and generate I/O, so run it in a maintenance window. SAMPLE n PERCENT is the compromise. Once you have used a sample rate you like, PERSIST_SAMPLE_PERCENT = ON (SQL Server 2016 SP1 CU4 / 2017 CU1) makes subsequent auto updates keep it.

For the whole database, sp_updatestats walks every table and updates only the statistics that have modifications since the last update:

USE Sales;
EXEC sp_updatestats;

It uses default sampling, so it's fast but not thorough. I run it right after a big data load or a migration and don't expect more from it.

Scheduling

Index rebuilds update the statistics of the rebuilt index with a full scan, but they do nothing for column statistics or for indexes that were only reorganized. So a maintenance routine that only rebuilds fragmented indexes leaves plenty of statistics stale.

If you use Ola Hallengren's IndexOptimize, this is the combination I use in the nightly Agent job:

EXECUTE dbo.IndexOptimize
    @Databases = 'USER_DATABASES',
    @FragmentationLow = NULL,
    @FragmentationMedium = 'INDEX_REORGANIZE,INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
    @FragmentationHigh = 'INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
    @UpdateStatistics = 'ALL',
    @OnlyModifiedStatistics = 'Y',
    @StatisticsSample = 100;

Without that toolkit, a Maintenance Plan Update Statistics Task with "All existing statistics" and "Full scan" achieves roughly the same, though it updates everything regardless of modifications.

The ascending key problem

Tables keyed on a date or an identity column get all their new rows beyond the highest step of the histogram. Until the next update, any query on "today's rows" estimates 1 row. Before SQL Server 2014 the fix was trace flags 2389 and 2390; with the new cardinality estimator (compatibility level 120 and above) the optimizer assumes rows exist beyond the histogram and the problem is largely gone. On older compatibility levels a more frequent targeted update on that one statistic is the practical fix:

UPDATE STATISTICS dbo.Orders IX_Orders_OrderDate WITH FULLSCAN;

Did the estimates get better?

Run the suspect query with the actual plan and compare estimated versus actual rows on the scan or seek operators. After a FULLSCAN update the numbers should be close. DBCC SHOW_STATISTICS ('dbo.Orders', IX_Orders_OrderDate) WITH HISTOGRAM shows the highest RANGE_HI_KEY step, which should now cover the latest data.

Things to watch for

• "Warning: Columns with no statistics" in an execution plan means AUTO_CREATE_STATISTICS is off or the query hit a column on a freshly loaded table. Turn auto create on; it is cheap.
• Updating statistics invalidates cached plans that reference the table, causing a recompile on next execution. On a busy system with thousands of ad hoc plans that shows up as a CPU spike right after the maintenance job. Spread updates out or run them when the system is quiet.
• sp_updatestats reports "Statistics for all tables have been updated" even if it only touched a few; read the per-table lines above it to see what really changed.
• A statistics update does not help if the estimate is off because of a parameter sniffing problem or a local variable. Check the plan's ParameterCompiledValue before blaming stats.

Before anyone asks for more RAM, check the statistics. It's the cheapest fix there is.