Asynchronous statistics auto update in SQL Server would ensure that the statistics are not updated when a query is executed, ensuring timely response. For this option, we need to set AUTO_UPDATE_STATISTICS_ASYNC to ON and also AUTO_UPDATE_STATISTICS to ON. Here, a separate thread runs in the background, which actually does the job of updating the statistics. So updated stats are available the next time for a query. This can also work the way round, meaning that a query might be using old stats and hence would be taking more time.

Synchronous statistics auto update in SQL Server would ensure that the stats are up-to date, every time a query is executed. This can be very much time consuming and is in proportion with the number of rows in a table. For this option, we need to set AUTO_UPDATE_STATISTICS_ASYNC to OFF.

Wise decisions had to made considering the scenario. For me, it helped me a lot by turning AUTO_UPDATE_STATISTICS_ASYNC to ON. This query below can be used to get the statistics status across all the databases in an instance.

SELECT name AS "Name",

is_auto_create_stats_on AS "Auto Create Stats",

is_auto_update_stats_on AS "Auto Update Stats",

is_auto_update_stats_async_on AS "Asynchronous Update"

FROM sys.databases

GO

The below queries can be used to set the ASYNC on.

ALTERDATABASE <DB_NAME> SET AUTO_UPDATE_STATISTICS ON

ALTERDATABASE <DB_NAME> SET AUTO_UPDATE_STATISTICS_ASYNC ON

This article has been prepared by referring to various other sources in the internet. Here are they: