AUTO_UPDATE_STATISTICS_ASYNC SQL Server Database Configuration

By:   |   Comments   |   Related: > Maintenance

Updating Statistics is critical for the SQL Server optimizer to have the latest information about the database to select the best query plan. Unfortunately, I am hesitant to enable AUTO UPDATE STATISTICS at a database level or for specific tables. I am concerned that this command could run during core business hours or during a big batch operation. I also have limited time to perform this maintenance as well as regularly scheduled maintenance. Does SQL Server 2005 offer any new features over SQL Server 2000 that will give me the confidence to enable AUTO UPDATE STATISTICS at a database level? Can I rest assured that my queries will not get blocked and my performance will not be impacted when the statistics are updated?

With SQL Server 2005, the AUTO_UPDATE_STATISTICS_ASYNC database configuration option configures a given database to update statistics asynchronously vs. synchronously. Updating statistics synchronously is the default behavior in both SQL Server 2000 and 2005. Typically, if a given query request triggers an AUTO UPDATE STATISTICS event without this option set, the query will wait as statistics are updated. Once the statistics are updated, then the query will be executed. However, if you set this option (AUTO_UPDATE_STATISTICS_ASYNC) the query will be executed against the existing statistics. At the same time, while a background request will tell the database engine to update the statistics automatically as soon as possible, without preventing any query request(s). As soon as the background operation completes, new query requests will begin to use the new statistics information. One important note is that this option only comes in to play for AUTO UPDATE STATISTICS events, not manual updating or on-demand updating of the statistics on request by a user.


sp_helpdb YourDatabaseName
-- Must have AUTO_UPDATE_STATISTICS enabled first


In many scenarios, the AUTO_UPDATE_STATISTICS_ASYNC option can be enabled with little risk or negative side-effects that stale-statistics can often cause i.e. poor query plans.  If your data distribution and workload typically have little impact on overall data distribution numbers, row counts and sizes, etc., then it is probably a great candidate for seeing nothing but improved throughput with this option enabled.


It is necessary to have AUTO_UPDATE_STATISTICS enabled first before enabling AUTO_UPDATE_STATISTICS_ASYNC because AUTO_UPDATE_STATISTICS_ASYNC is dependent on AUTO_UPDATE_STATISTICS.

In addition, Adam Machanic of the community indicated that the AUTO_UPDATE_STATISTICS_ASYNC may be a favorable configuration for OLTP environments and may not be for data warehouse environments.  The specific scenario is related to databases that have a lot of data modification throughout the day.  He reports that he has observed the AUTO_UPDATE_STATISTICS_ASYNC configuration cause major issues in two data warehouse systems that involved near-real time loads.  In both cases, a similar pattern occurred: queries against a recent time frame hit just after the load, did not trigger a synchronous update and the query optimizer chose a seek where a scan would have been more appropriate.  This is due to the increased data density in that range.  The bottom was really bad performance in both cases until AUTO_UPDATE_STATISTICS_ASYNC was disabled.

As with all SQL Server configurations and application scenarios, it is necessary to test the changes and validate the advantages and disadvantages based on your application and environment.

Next Steps

sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip

About the author
MSSQLTips author Chad Boyd Chad Boyd is an Architect, Administrator and Developer with technologies such as SQL Server, .NET, and Windows Server.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Comments For This Article

get free sql tips
agree to terms