Recently I was doing Performance Optimization on a client’s environment and noticed that their DBAs have completely turned off the AUTO UPDATE STATS as their client application was timing out whenever Statistics were getting updated in the background. They were manually updating the statistics every month.
SQL Server needs Statistics to build better execution plans. Statistics are histograms about the distribution of data in the fields and are being used by SQL for execution plans. By default, statistics are updated when 20% of the data in a table changes – and it is done whenever a query needs to read data from that table.
Till SQL Server 2000, the auto update stats happened synchronously only. So what I want to emphasize here is that from SQL Server 2005 onward, we have the ability to do it asynchronously as well. The default is synchronous though. This option is useful for scenarios where we had to turn off Auto Update Stats and hence all the queries were using stale statistics.
Instead if we turn on Auto Update Stats Asynchronously then SQL Server will start updating the stats in background . All the client queries won’t be waiting for the process to complete and they can continue using the old stats while the update stats is under process in background. When the update process is complete, the optimizer can now start using the new\updated stats.
Lastly, lets see how to enable this option:
— Enabling the Auto Update Stats Async
ALTER DATABASE AdventureWorksLT2008R2 SET AUTO_UPDATE_STATISTICS ON
ALTER DATABASE AdventureWorksLT2008R2 SET AUTO_UPDATE_STATISTICS_ASYNC ON
— Disabling the Auto Update Stats Async
ALTER DATABASE AdventureWorksLT2008R2 SET AUTO_UPDATE_STATISTICS_ASYNC OFF
Hopefully, this will clear some doubts.