Learn more about SQL Server tools



solving sql server problems for millions of dbas and developers since 2006
join MSSQLTips for free SQL Server tips

































Top SQL Server Tools






















SQL Server's Auto Update Statistics Async option

MSSQLTips author Tibor Nagy By:   |   Read Comments (1)   |   Related Tips: More > 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.

SQL Server statistics

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


Last Update: 3/12/2013


About the author
MSSQLTips author Tibor Nagy
Tibor Nagy is a technical and business professional from the financial industry. His experience includes SQL Server 2000-2008, DB2 and MySQL.

View all my tips
Related Resources


print tip Print  
Become a paid author




Recommended For You








Learn more about SQL Server tools
Comments and Feedback:
Thursday, May 16, 2013 - 2:01:04 PM - Srinath Read The Tip

Nice post...!!! Cleanly written !!



Post a Comment or Question

Keep it clean and stay on the subject or we may delete your comment.
Your email address is not published. Required fields are marked with an asterisk (*)

*Name   *Email Notify for updates

Signup for our newsletter


Comments
*Enter Code refresh code


 
Sponsor Information







Copyright (c) 2006-2014 Edgewood Solutions, LLC All rights reserved
privacy | disclaimer | copyright | advertise | about
authors | contribute | feedback | giveaways | user groups | community | events | first timer?
Some names and products listed are the registered trademarks of their respective owners.