One of the things I’ve been able to implement to help with performance is changing from Update Statistics Synchronous to Auto Update Statistics Asynchronously. It’s a simple change that can have a big impact when implemented in highly transactional OLTP environments. Notice I said OLTP not OLAP, since data in an OLAP environment tends to not be as dynamic, so it’s rare to enable this in a data warehouse.
So, what’s the difference between the two and why does it help?
Synchronous (defaulted as AUTO_UPDATE_STATISTICS =TRUE)
By default, when Auto Update Statistics is set to True, the SQL Server Query Optimizer will automatically update statistics when data has met a threshold of changes (insert, update, delete, or merge) and the estimated rows are now potentially stale. When statistics are stale, execution plans can become suboptimal which can lead to degradation in performance.
This best practice option ensures your statistics stay up to date as much as possible. Each time a cached query plan is executed the Optimizer checks for data changes and potentially generates new statistics. This behavior is exactly what we want, but there is a catch. The caveat to this is that a cached query plan will be “held” while the statistics are updated and will recompile to use the new values before running. This caveat can slow down the execution process dramatically.
Auto Update Statistics Asynchronously (AUTO_UPDATE_STATISTICS_ASYNC =TRUE)
This option does the same thing as the above, but with one significant difference. It allows the Optimizer to run a query and then use the updated the statistics. Where this option differs from synchronous is that a query will NOT be “held” while the statistics are updated. Queries can run “as is” until the query optimizer completes the statistics updates and then the query will recompile to begin to use them the next time it runs.
Confused Yet, so now in English. 
When the Asynchronous setting is set the query will run like it is until all statistics its uses are up-to-date, then it will run with the new numbers. It does not have to wait for all the new numbers to be updated to run. That’s where you get your performance boost, by not having to wait.
Check your settings using TSQL on ALL Databases
SELECT name, is_auto_update_stats_on, is_auto_update_stats_async_on FROM sys.databases
How to Turn it on TSQL
ALTER DATABASE MyDatabase SET AUTO_UPDATE_STATISTICS_ASYNC ON
GUI
Under Database Properties > Options
NOTE: To enable this option Auto Update Statistics must be left ON.
Last Words
Remember every environment is different be sure to test this before implementing into production. A simple change from synchronous to asynchronous can make a difference. It is definitely something to add to your performance tuning tool belt.
Pingback: Asynchronous Stats Updates – Curated SQL
Thanks for the great post. It couldn’t have been explained better.
However, in the ‘How to Turn it on TSQL’ section, I believe you meant:
ALTER DATABASE MyDatabaseName
SET AUTO_UPDATE_STATISTICS_ASYNC ON
GO
Wow. I hadn’t had this option explained previously and took little notice. Thank you. This goes in my installation checks and scripts.
Hi,
Thanks for the post. What do you look at to come to the conclusion to make this change. Are you looking at compile time in the plan or is there a specific XE you’re looking at or something else?
I too am interested in implementing this, but I need a metric to measure before and after the change to show that it has created a performance improvement. What metric would that be and how would I capture it? Thanks!