In SQL Server, when does AUTO_UPDATE_STATISTICS actually refresh a statistics object, and why can a large table's estimates still go stale?
answer
- counted modifications against a threshold
- checked when a query compiles
- compatibility level 130 changed the formula
- MIN(500 + 20%, SQRT(1000 * n))
basics
~20 sSQL Server refreshes a statistic only when a query using it compiles after modifications pass a threshold: 500 + 20% of rows below compatibility level 130, and the smaller of that and SQRT(1000 * rows) from 130.
solid answer
~40 s`AUTO_UPDATE_STATISTICS` is ON by default, but nothing runs on a timer. SQL Server counts row modifications per statistics object and, when a query that uses the statistic compiles or a cached plan is about to run, compares the count with a threshold. Below compatibility level 130 the threshold is `500 + 0.20 * n`; from 130 it is `MIN(500 + 0.20 * n, SQRT(1000 * n))`, so a 2-million-row table updates after about 44,721 changes instead of 400,500. By default the update is synchronous: the query waits for it. With `AUTO_UPDATE_STATISTICS_ASYNC` the query compiles with the old statistic and a background request refreshes it. Estimates still go stale between thresholds, after sampled updates on skewed data, or when no query touches the table. I check `sys.dm_db_stats_properties` for `last_updated`, `rows_sampled` and `modification_counter`.
code
sql · 11 linesSELECT s.name AS statistics_name,
sp.last_updated,
sp.rows,
sp.rows_sampled,
sp.modification_counter
FROM sys.stats AS s
CROSS APPLY sys.dm_db_stats_properties(s.object_id, s.stats_id) AS sp
WHERE s.object_id = OBJECT_ID(N'dbo.Orders')
ORDER BY sp.modification_counter DESC;
UPDATE STATISTICS dbo.Orders WITH FULLSCAN;go deeper
Recall that SQL Server creates and updates statistics automatically by default, and that stale statistics cause bad row estimates.
Explain the modification counter, both threshold formulas, the role of compatibility level 130, and synchronous versus asynchronous updates.
Diagnose staleness with sys.dm_db_stats_properties and the plan's OptimizerStatsUsage, and pick between FULLSCAN, persisted sample rates and post-load updates.
Set a fleet policy for statistics maintenance on huge tables and decide where the async option's latency gain is worth its blocking risk.
## What the automatic options do SQL Server keeps **statistics objects**: a histogram on the first key column plus density information. Every index has one, the optimizer adds single-column ones as it needs them, and you can create more with `CREATE STATISTICS`. Two database options manage them, and both default to `ON`: - `AUTO_CREATE_STATISTICS` creates single-column statistics on predicate columns that have no histogram yet. These are named with a `_WA` prefix. It does not decide whether indexes get statistics (they always do) and never creates filtered statistics. - `AUTO_UPDATE_STATISTICS` refreshes an existing statistic when the optimizer judges it out of date. It applies to index statistics, auto-created column statistics, statistics made with `CREATE STATISTICS`, and filtered statistics. The update is **not scheduled**. SQL Server counts modifications from `INSERT`, `UPDATE`, `DELETE` and `MERGE` since the statistic was last updated. When a query that uses that statistic compiles, or before a cached plan runs, it compares the count with a threshold. A table nobody queries keeps its old statistics however much it changes. ## The threshold and the compatibility level | Compatibility level | Rows in table (n) | Modifications before an update | | --- | --- | --- | | below 130 | n > 500 | `500 + 0.20 * n` | | 130 and later | n > 500 | `MIN(500 + 0.20 * n, SQRT(1000 * n))` | | any | permanent table, n <= 500 | 500 | Worked through for a 2,000,000-row table: 1. Legacy formula: `500 + 0.20 * 2,000,000 = 400,500` modifications. 2. Dynamic formula: `SQRT(1000 * 2,000,000)` is about **44,721**, and the smaller value wins. The dynamic threshold arrived with SQL Server 2016 at compatibility level 130. A database kept at 120 or lower after an upgrade keeps the old 20% rule unless trace flag 2371 is on. Even the dynamic rule lets a billion-row table absorb about a million changes before an update. If those rows are all new keys beyond the histogram's last step, the histogram has no step for them, so their estimates are guesses rather than counts. ## Synchronous versus asynchronous updates By default the update is **synchronous**: the compiling query waits for the new statistic, then compiles with it. On a large table that wait can exceed an application's command timeout, and the query that tripped the threshold pays for everyone. `AUTO_UPDATE_STATISTICS_ASYNC` (default `OFF`, and only meaningful with `AUTO_UPDATE_STATISTICS` on) lets the query compile with the existing statistic while a background request refreshes it. Later compilations benefit. The trade-offs: - The query that triggered the update may get a plan built on stale numbers. - Statistics on local temporary tables are always updated synchronously, whatever the option says. - To publish the new statistic, the background request needs a schema modification lock on the statistics metadata. Under heavy compilation it can block, and be blocked by, sessions taking schema stability locks to compile. From SQL Server 2022 the `ASYNC_STATS_UPDATE_WAIT_AT_LOW_PRIORITY` database scoped configuration makes it wait on a low-priority queue instead. ## Checking freshness and fixing it - `sys.dm_db_stats_properties(object_id, stats_id)` returns `last_updated`, `rows`, `rows_sampled` and `modification_counter` for one statistic. - `sys.databases` exposes `is_auto_create_stats_on`, `is_auto_update_stats_on` and `is_auto_update_stats_async_on`. - An execution plan's `OptimizerStatsUsage` element lists each statistic used at compile time with its `ModificationCount`, `SamplingPercent` and `LastUpdate`. When estimates are wrong, the usual levers are: 1. `UPDATE STATISTICS table WITH FULLSCAN` when a default sample misses skew; a lower sampling percent can give a less accurate histogram on skewed data. 2. `PERSIST_SAMPLE_PERCENT = ON` so later automatic updates keep the sample rate you chose rather than falling back to the default. 3. An explicit `UPDATE STATISTICS` step at the end of a bulk load, so the first queries after it do not compile against yesterday's histogram. An update to a statistic also makes plans that depend on it recompile, so refreshing statistics often looks like it fixed a parameter-sensitive procedure. That is a side effect of the new compile, not proof that statistics were the cause. How histograms, density and sampling are built is the general material on optimizer statistics.
- Your database was upgraded in place from an old release and still runs at compatibility level 120. What does that mean for statistics?Compatibility levels below 130 keep the legacy `500 + 20%` threshold, so large tables refresh rarely. Raising the level to 130 or higher switches to `MIN(500 + 0.20 * n, SQRT(1000 * n))`, but it also changes optimizer behaviour, so it belongs in a planned upgrade. Trace flag 2371 gives the dynamic threshold without changing the level.
- When would you turn on AUTO_UPDATE_STATISTICS_ASYNC?When synchronous updates on large tables cause unpredictable latency or client timeouts for frequently run queries. Those queries then compile with existing statistics and a background request refreshes them. Avoid it for workloads that change data distribution in bulk and query immediately, and on busy compilers consider `ASYNC_STATS_UPDATE_WAIT_AT_LOW_PRIORITY` (SQL Server 2022 and later) to avoid schema-lock blocking.
- Says statistics update after 20 percent of rows change at every compatibility level.
- Thinks AUTO_UPDATE_STATISTICS refreshes statistics in the background on a schedule.
- Believes AUTO_UPDATE_STATISTICS_ASYNC makes estimates more accurate.
- Assumes a sampled update is always as accurate as WITH FULLSCAN.
- Thinks AUTO_CREATE_STATISTICS is what gives new indexes their statistics.
Assumes SQL Server 2025 at compatibility level 130 or higher for the dynamic threshold; ASYNC_STATS_UPDATE_WAIT_AT_LOW_PRIORITY needs SQL Server 2022 or later.