By: Tibor Nagy | Comments (1) | Related: > Indexing
Problem
I have been trying to tune my SQL Server and I noticed a few database settings for statistics. I also read the previous tip on automatic statistics updates. Could you please describe the Auto Create Statistics Async option? How is this configured and when should I use it and when should I avoid using it?
Solution
The AUTO_UPDATE_STATISTICS_ASYNC option affects how automatic statistics updates are applied to your SQL Server database. This option is set database by database and the default setting for this option is disabled.
When this option is enabled, the Query Optimizer will not wait for the update of statistics, but will run the query first and update the outdated statistics afterwards. Your query will execute with the current statistics and a background process will start to update the statistics in a separate thread. It should be noted that the Query Optimizer may choose a suboptimal query plan if statistics are outdated when the query compiles. When this background operation is complete, the new query requests will use the new updated statistics.
When this option is disabled, the Query Optimizer will update the outdated statistics before compiling the query therefore possibly getting a better plan based on the most current statistics. This is referred to as synchronous statistics updates.
When to Use Asynchronous Statistics?
This option enables faster plan generation, but may create sub-optimal query plans because the plan compilation may be based on stale statistics.
Asynchronous statistics can be beneficial if the statistics update requires a significant amount of time and your queries cannot wait for it to complete.
You can get more predictable query response times with asynchronous statistics updates if your SQL Server frequently executes the same query or similar cached query plans.
The Query Optimizer can execute your queries without waiting for updated statistics when the AUTO_UPDATE_STATISTICS_ASYNC option is used. This way none of your queries will be delayed when the statistics are updated. It is also possible that some applications experience timeouts when the statistics are updated before running the query. This situation can be avoided by enabling asynchronous statistics updates.
When Not to Use Asynchronous Statistics?
It is recommended to disable the AUTO_UPDATE_STATISTICS_ASYNC option when your SQL Server executes operations that significantly change the distribution of your data, like truncating a table or running bulk updates on a large percentage of the rows.
Using synchronous statistics will ensure statistics are up-to-date before executing queries on the changed data. It is recommended to use this when your queries can wait until the statistics are updated. In most cases this is very minimal and you will not notice this, that is why this is the default behavior.
Checking and Setting this Option
We can first check the settings for all of our databases using this query.
SELECT name, is_auto_update_stats_on, is_auto_update_stats_async_on FROM sys.databases
You will receive results in a format like the screenshot below. A value of 1 is enabled and 0 is disabled.
Note: to use AUTO_UPDATE_STATISTICS_ASYNC, the AUTO_UPDATE_STATISTICS needs to be enabled as well.
You can use this command to enable Auto Update Statistics Async:
ALTER DATABASE YourDBName
SET AUTO_UPDATE_STATISTICS_ASYNC ON
You can use this command to enable Auto Update Statistics:
ALTER DATABASE YourDBName
SET AUTO_UPDATE_STATISTICS ON
Which Option is better for my Database?
It depends on the specifics of your installation, on the table sizes and number of indexes, the frequency of data change, the queries run, etc...
Generally, the AUTO_UPDATE_STATISTICS_ASYNC may be a recommended option for online transaction processing (OLTP) environments and it is less beneficial for data warehouse systems.
You should weigh the pros and cons of this option before changing your current settings. The benefits and disadvantages can be unpredictable if your server mixes OLTP and data warehousing functions. Also, I recommend always running tests before you change something on your production environment.
Next Steps
- Check out these related tips to learn more about SQL Server statistics and indexing:
- Performance Tuning Tips category articles
- Indexing Tips category articles
- Read more tips by the author here
About the author
Tibor Nagy is a SQL Server professional in the financial industry with experience in SQL 2000-2012, DB2 and MySQL.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
