Asynchronous Statistics Updates: When They Help and When They Hurt

The query is short, but compilation can still wait for a statistics refresh. Asynchronous statistics updates let that compilation proceed with existing statistics while a background request performs the refresh.

What Asynchronous Statistics Updates Move to the Background

Automatic statistics maintenance uses modification information to decide when an update is needed. With the ordinary synchronous setting, a qualifying compilation waits for the automatic refresh. With the asynchronous setting, the current compilation can use existing statistics while the update runs separately. Later compilations can use the refreshed information.

The setting does not turn query execution into background work. It also does not make an explicit UPDATE STATISTICS statement asynchronous. Automatic creation and automatic updating are distinct behaviors. Check the database configuration and the actual trigger for the pause before assuming this option addresses it.

I identify the compile-related delay before changing the setting. A slow scan, a blocked data modification, or a missing index is a different investigation. Removing one wait during compilation can leave the resulting query with a less suitable estimate. The application still pays for that plan when it executes.

Inspect the Existing Database Configuration

Read the database's automatic statistics options and the available scoped configuration. The low-priority option discussed later is available in SQL Server 2022 and later. A missing row on an older deployment is a version boundary rather than evidence that the feature is disabled.

SELECT name,compatibility_level,is_auto_create_stats_on,
       is_auto_update_stats_on,is_auto_update_stats_async_on
FROM sys.databases WHERE database_id=DB_ID();
SELECT name,value,value_for_secondary
FROM sys.database_scoped_configurations
WHERE name=N'ASYNC_STATS_UPDATE_WAIT_AT_LOW_PRIORITY';

Record the previous values with the workload baseline. If automatic updating is disabled, enabling its asynchronous mode alone does not create the intended maintenance behavior. Review the current policy and any manually scheduled statistics jobs. Multiple maintenance mechanisms can overlap, so the operating record should explain which process updates each important statistic.

Capture representative compilation and execution behavior before the trial. Use the actual application parameter patterns and data-change schedule. An isolated fast execution with a warm plan cannot demonstrate what happens when an important statistic first crosses its update threshold.

Look at the Statistics That Influence the Query

Inspect the relevant statistics rather than treating the entire table as one aging object. The properties function exposes the last update, sampled rows, and modification counter. It can return no row when statistics information is unavailable, so use OUTER APPLY to retain the inventory.

SELECT s.name AS StatisticName,s.stats_id,s.auto_created,s.user_created,
       p.last_updated,p.rows,p.rows_sampled,p.modification_counter
FROM sys.stats AS s
OUTER APPLY sys.dm_db_stats_properties(s.object_id,s.stats_id) AS p
WHERE s.object_id=OBJECT_ID(N'dbo.OrderLines');

The illustrated table must already exist in the approved lab. Inspect its definition and workload before updating it. Modification counters are not a universal measure of how every column distribution changed. A concentrated change in a predicate's relevant range can matter even when a broad percentage looks modest.

Review estimates and actual rows on the query's important operators. A last_updated timestamp alone cannot certify that a statistic is adequate. Sampling, data skew, ascending values, and the scope of the statistic all affect its usefulness. Keep the distribution problem connected to the specific query it influences.

Trial Asynchronous Statistics Updates on Short Queries

For a workload of short requests where occasional synchronous refresh delays are unacceptable, a reviewed trial can be useful. The following commands apply the setting to an existing disposable database. They are configuration changes, not merely diagnostic queries.

ALTER DATABASE StatsLab SET AUTO_UPDATE_STATISTICS ON;
ALTER DATABASE StatsLab SET AUTO_UPDATE_STATISTICS_ASYNC ON;

Execute representative requests after the same accepted data changes and compare both compile delay and total request latency. Include the first request after a refresh becomes necessary and later requests after the background update. Do not infer the refresh completed after an arbitrary short sleep. Recheck the statistic's properties and the observed plan behavior.

Keep a reversal plan using the recorded baseline values. Test concurrency as well as individual duration. A design can reduce the first request's wait while introducing another contention pattern during background maintenance. The useful outcome is improved service behavior, not simply a setting displayed as ON.

When Asynchronous Statistics Updates Hurt After a Big Load

A bulk load or major distribution change can leave old statistics unsuitable for a report that runs immediately afterward. Allowing that report to compile before refreshed statistics are available can produce poor estimates and an expensive plan. In this case, deliberate maintenance before the report can be a clearer contract.

UPDATE STATISTICS dbo.OrderLines WITH FULLSCAN;
SELECT s.name,p.last_updated,p.rows,p.rows_sampled,p.modification_counter
FROM sys.stats AS s
OUTER APPLY sys.dm_db_stats_properties(s.object_id,s.stats_id) AS p
WHERE s.object_id=OBJECT_ID(N'dbo.OrderLines');

FULLSCAN has a cost and requires an approved maintenance window for the real table. A targeted statistic or appropriate sample can be preferable when a complete scan is excessive. Validate the resulting estimates rather than choosing FULLSCAN as a ritual. The important requirement is that the relevant information is refreshed before the dependent workload needs it.

Which report runs immediately after the largest load? Include that sequence in the setting trial. I do not trade a small compilation pause for an unexamined long-running report. Keep asynchronous statistics updates aligned with the workload's tolerance for temporarily using existing estimates.

Consider Low-Priority Metadata Locking

A background statistics update needs to persist its new metadata. Acquiring the required schema-modification lock can interact with concurrent compilations. In supported versions, the low-priority scoped option moves that wait to a separate low-priority queue so other compilations can continue using existing information.

ALTER DATABASE SCOPED CONFIGURATION
SET ASYNC_STATS_UPDATE_WAIT_AT_LOW_PRIORITY=ON;

Run that statement in the intended SQL Server 2022 or later lab database after review. If the background request cannot acquire the lock within its supported timeout behavior, the update can be abandoned until a later trigger or manual update. The setting does not forcibly kill other sessions to guarantee immediate refresh.

Observe whether important statistics actually become current under sustained compilation traffic. A quieter lock queue is useful, but repeatedly deferred refresh still needs an operating response. Keep the low-priority setting, update outcome, and application latency together in the evidence. Background maintenance does not become invisible maintenance.

Decide From the Complete Workload Sequence

Compare the original and trial settings across routine requests, peak concurrency, bulk changes, and immediate reports. Retain measured compile behavior, execution performance, and refresh completion. Restore the accepted baseline if the trial fails its service criteria. Keep manual maintenance scoped to the statistics that need it.

Asynchronous statistics updates are useful when the workload benefits from avoiding the synchronous refresh pause and tolerates the temporary estimates. They are less useful when current distribution information is essential before the next expensive query. Choose the behavior from that sequence rather than adopting the option as a universal performance fix.

Related reading on this blog: The Comprehensive Guide to STATISTICS_NORECOMPUTE and Understanding Incremental Statistics.

An asynchronous refresh is not free statistics maintenance, it is a choice about when queries use new distribution information.

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