When Auto Update Statistics Is Not Enough

The optimizer can choose a poor plan even when auto update statistics is on. Knowing when auto update statistics is not enough means checking how current the sampled picture really is.

Know What Auto Update Statistics Does

SQL Server tracks modifications and updates a statistic when its threshold is reached and the statistic is needed for compilation. The threshold behavior depends on table size, compatibility level, and current engine behavior. Auto update statistics is valuable and should normally remain enabled. It cannot promise that every statistic reflects the latest distribution at every moment.

I start by checking whether the setting is on and whether the problem is actually an estimate problem. A slow query can come from blocking, missing indexes, or parameter-sensitive plans. Updating every statistic because one query slowed down adds work without identifying the cause. The plan’s estimated and actual rows provide a better first clue.

Inspect Auto Update Statistics Database Settings

The database options show whether automatic creation and update of statistics are enabled, and whether asynchronous updates are configured. With asynchronous updates, a compiling query can use an older statistic while a background update begins. That trade-off can reduce compile waits but leave one execution on the old estimate. Decide based on workload behavior.

I record these options before recommending a schedule. A restored database can carry settings from a different environment. The query is read-only and covers user databases on the instance. It does not tell you which individual statistic needs attention.

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

Read Modification Evidence

sys.dm_db_stats_properties exposes last updated time, rows, sampled rows, and modification counter for a statistic. Run it in the database containing the table. Compare the metadata with workload changes and the query plan. A large modification count can matter more on a small selective subset than on the table overall.

I inspect the statistic used by the slow plan rather than every object in the database. The query below lists properties for statistics on one table. Replace the table name with a real object. Null properties need investigation, especially when a statistic has not been populated.

SELECT s.name AS statistic_name,
       p.last_updated,
       p.rows,
       p.rows_sampled,
       p.modification_counter
FROM sys.stats AS s
CROSS APPLY sys.dm_db_stats_properties(s.object_id, s.stats_id) AS p
WHERE s.object_id = OBJECT_ID(N'dbo.YourTable')
ORDER BY s.name;

Watch Ascending Keys

A statistic on a date or identity column can describe the old range well while new values keep arriving beyond the histogram’s upper bound. The optimizer has features to handle ascending keys, but its estimate still depends on version, compatibility level, and data pattern. Check the plan for predicates on recent values and compare estimates with actual rows.

I have seen a query for today’s records misestimated while a query for last month behaved well. That contrast points toward a changing edge of the distribution. It does not prove the statistic is stale by itself. Inspect the histogram, last update, and actual workload before scheduling manual updates. A newly arriving range needs timely information, not a ritual rebuild of every index.

Understand Sampling

Automatic updates use a sample in many cases. A sample captures broad distribution efficiently but can miss a rare value or skewed subset that matters to one query. FULLSCAN can help a targeted statistic, at the cost of reading more data and using maintenance time. A persisted sample rate can make future updates more consistent when justified.

I choose sampling based on a specific estimate problem. A full scan of every statistic on every large table is not a sustainable default. Test whether a targeted update changes the estimate and plan for the query you care about. Keep the previous plan and timing evidence. A statistic can be perfectly fresh and still be a poor model of correlated columns.

Update a Specific Statistic

UPDATE STATISTICS lets you refresh one statistic without rebuilding an index. The example uses a sample rate for a named statistic. Replace names and sampling choice after checking the table. Run it in the database that owns the object. A targeted change is easier to judge than a broad maintenance job.

I check the query plan after the update. If the estimate remains wrong, the issue can be parameter sensitivity, correlation, or predicate design. Do not keep repeating the same update hoping the optimizer will change its mind. A sampled histogram has limits that more frequent refreshes do not erase.

UPDATE STATISTICS dbo.YourTable YourStatistic
WITH SAMPLE 50 PERCENT;

Schedule Around Data Loads

A large import or nightly batch can change distribution sharply before auto update triggers for the relevant statistic. Refresh selected statistics after that load, before dependent reports start. Put the step in the same operational workflow as the load so it follows real data change, not only the clock. Monitor its duration and job result.

I prefer a short list of proven sensitive statistics. A maintenance schedule that updates everything each night can spend hours reading data with little benefit. The right schedule is tied to known changes and query needs. Document why each statistic is on it. Review the list when the application changes.

Compare Estimates and Results

Estimated versus actual row counts are the central test. Look at the plan operator where they diverge, then identify the statistic and predicate involved. An overall query duration change can have many causes. Row estimate evidence links the statistic to a plan choice. Query Store can help compare plans before and after a targeted update.

I ask whether the plan improved for the full range of parameters, not just one test value. A new statistic can favor one common case while hurting another. Test representative inputs. If the data is strongly skewed, filtered statistics or query design can be more useful than a higher update frequency.

Keep Auto Update Statistics as the Base

Manual updates should complement automatic maintenance, not replace it without a clear reason. Leave automatic creation and update enabled unless a tested workload requirement says otherwise. Document special schedules and remove ones that no longer help. Check that the statistic remains relevant after schema and query changes.

Which query would fail its performance goal if this statistic stayed one load behind? Answer that and you have a candidate for targeted maintenance. Without a query and a measured estimate gap, a mass update is only activity. Good statistics work is selective, observed, and revisited.

Related reading on this blog: Find Oldest Updated Statistics: Outdated Statistics and Not Auto-Updating Statistics with STATISTICS_NORECOMPUTE.

An enabled auto update switch is not a guarantee of a useful estimate, it is the base of a targeted statistics plan.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.