This extra connection is what can get people into some trouble. The auto update statistics async option applies to statistics objects created for indexes single columns in query predicates and statistics created with the create statistics statement.
If you find that your queries are waiting for auto update stats to complete then having this turn on async will help because there is a extra connection that the system opens to the database to perform the statistics updates asynchronously.
Auto update statistics asynchronously. By default this happens automatically statistics are updated when about. Auto update statistics asynchronous will minimize the blocking. Statistics updates can be either synchronous the default or asynchronous.
When this option is enabled. This option is set database by database and the default setting for this option is disabled. Auto update stats async enabled sql server maintains statistics that help it decide how to build better execution plans.
In many scenarios the auto update statistics async option can be enabled with little risk or negative side effects that stale statistics can often cause i e. If your data distribution and workload typically have little impact on overall data distribution numbers row counts and sizes etc then it is probably a great candidate for. Auto update statistics asynchronously auto update statistics async true this option does the same thing as the above but with one significant difference.
If it is executing in synchronous mode it puts a hold on execution plan compile. It allows the optimizer to run a query and then use the updated the statistics. It periodically reviews the content of tables updates its statistics about the distribution of data in fields and uses that for execution plans.
If sql server determines that a statistic is out of date during it will update it. The auto update statistics async option affects how automatic statistics updates are applied to your sql server database.