When it comes to optimizing the performance of your SQL Server database, there are various techniques and best practices that you can implement. One such technique is changing from Update Statistics Synchronous to Auto Update Statistics Asynchronously. This simple change can have a significant impact, especially in highly transactional OLTP environments.

Before we dive into the details, let’s understand the difference between synchronous and asynchronous update statistics and why it can help improve performance.

Synchronous Update Statistics

By default, SQL Server has Auto Update Statistics set to True, which means that the Query Optimizer will automatically update statistics when data has met a threshold of changes (insert, update, delete, or merge) and the estimated rows are potentially stale. This ensures that the statistics stay up to date as much as possible, which is crucial for optimal execution plans and performance.

However, there is a caveat to this approach. When statistics are updated synchronously, a cached query plan will be “held” while the statistics are updated. This means that the execution process can be significantly slowed down, as the query plan needs to be recompiled to use the new values before running.

Asynchronous Update Statistics

The Auto Update Statistics Asynchronously option does the same thing as synchronous update statistics, but with one significant difference. It allows the Query Optimizer to run a query and then use the updated statistics. The query will not be “held” while the statistics are updated, which means that queries can run “as is” until the Query Optimizer completes the statistics updates. The query will then recompile to begin using the updated statistics the next time it runs.

So, in simpler terms, when the asynchronous setting is enabled, the query will run with the existing statistics until they are up-to-date, and then it will run with the new statistics. This eliminates the need to wait for all the new statistics to be updated before running the query, resulting in a performance boost.

Enabling Asynchronous Update Statistics

To enable asynchronous update statistics, you can use either T-SQL or the GUI:

T-SQL:

ALTER DATABASE YourDatabaseName
SET AUTO_UPDATE_STATISTICS_ASYNC ON

GUI:

Under Database Properties > Options, make sure that Auto Update Statistics is enabled. Then, enable the Auto Update Statistics Asynchronously option.

It’s important to note that in order to enable the asynchronous option, Auto Update Statistics must be left ON.

Conclusion

Every SQL Server environment is unique, and what works for one may not work for another. Therefore, it’s crucial to test the impact of enabling asynchronous update statistics before implementing it in a production environment.

However, making a simple change from synchronous to asynchronous update statistics can make a significant difference in performance. It’s definitely a technique worth adding to your performance tuning tool belt.