The optimizer relies heavily on statistics to produce an efficient execution plan. There are many environments where I have seen that AUTO CREATE STATS and AUTO UPDATE STATS is turned on, and that’s the default too.
select is_auto_create_stats_on, is_auto_update_stats_on from sys.databases where name = 'AdventureWorks2008r2'
I have also seen many environments where DBA’s turn off AUTO UPDATE STATS and they update the stats manually as part of their maintenance activity – and a justified reason for that is, they do not want the client queries to keep waiting while the stats are being updated in the background automatically by SQL Server. May be, they faced this issue which triggered them to turn off AUTO UPDATE STATS. Frequency of manually updating the stats could depend on many factors and I would not like to talk about that right now.
What I want to highlight is the database option that was introduced in SQL Server 2005, AUTO UPDATE STATS ASYNCHRONOUSLY. This option is not known to many and could be useful for DBA’s who turn off auto update stats as one of the downside of doing so is that the queries will have to rely on old/out-of-date stats that could result in poor execution plans.
Instead, DBA’s can turn on AUTO UPDATE STATS ASYNCHRONOUSLY which is off by default.
select is_auto_update_stats_async_on from sys.databases where name = 'AdventureWorks2008r2'
This option when turn on, means that SQL Server will start updating the stats asynchronously using a separate background thread. Client queries will not have to wait for the process to complete and they can continue using the current stats while the update is under process in background. When the update process is complete, the optimizer can now start using the updated stats. Yes, this means that until the update process is complete, the optimizer will produce execution plans based on the current/old stats.
This option, though I have not seen a wide usage of it, can be handy rather than turning off auto update stats completely.
ALTER DATABASE AdventureWorks2008r2 SET AUTO_UPDATE_STATISTICS ON GO ALTER DATABASE AdventureWorks2008r2 SET AUTO_UPDATE_STATISTICS_ASYNC ON GO
Doesn’t this option help in reducing downtime? 😮 (if yes, then this option should come under High Availability 😉