SQL Server Interview: What is the difference between AUTO_UPDATE_STATISTICS and AUTO_UPDATE_STATISTICS_ASYNC

This article is half-done without your Comment! *** Please share your thoughts via Comment ***

SQL Server Database Statistics is one of the important topics in the Query optimization. I already wrote a couple of articles on SQL Server Database Statistics.

SQL Server: Enable Auto Create and Auto Update Statistics

In the Interview of SQL DBA, If you say like you are also good in Query Optimization, there is 99% chance that interviewer asks questions on SQL Database Statistics.

Why is Database Statistics important?

Simple answer – Query optimizer creates a query execution plan basis on Object Statistics information like record count, some real pages, page location. If statistics are not up to date, it generates the wrong query execution plan which will slow down the query performance.

How can we update Statistics automatically?

Statistics updates can be either synchronous (the default) or asynchronous.

AUTO_UPDATE_STATISTICS – For synchronous update:
This is a synchronous option, and if it is running, a query has to wait for updated statistics. Updating statistics synchronously is the default behaviour, and once the statistics are updated, then the query will be executed.

AUTO_UPDATE_STATISTICS_ASYNC – For asynchronous update:
This is an asynchronous option, and if it is running, a query hasn’t required waiting for updated statistics because the query will be executed against the existing statistics.

In the background, at the same time database statistics update automatically using AUTO_UPDATE_STATISTICS_ASYNC option.
Once the background statistics update process completes, the new query can use the newly updated database statistics.

Please note: Before enabling the AUTO_UPDATE_STATISTICS_ASYNC option, Must have AUTO_UPDATE_STATISTICS enabled first