Hi Friends,
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.
Sample code:
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 😉
How does AUTO_UPDATE_STATISTICS_ASYNC reduce downtime?
Liked it.
Some DBA’s does mistake by only enabling AUTO_UPDATE_STATISTICS_ASYNC to ON.
What I Mean is if AUTO_UPDATE_STATISTICS option is off and AUTO_UPDATE_STATISTICS_ASYNC is ON,
there is no effect of AUTO_UPDATE_STATISTICS_ASYNC.
It will work only if AUTO_UPDATE_STATISTICS is also set to ON.
SQL SERVER 2012 also doesn’t show any error or warning if you set AUTO_UPDATE_STATISTICS to OFF
and AUTO_UPDATE_STATISTICS_ASYNC to ON.
Thanks,
SQLCommitted
Hi Marc, Well my last statment is asking the audience whether it reduces the downtime or not? But before I give my opinion, I have a counter question. How does online re-indexing reduce downtime
Auto Update Statistics asynchronous will minimize the blocking. If SQL Server determines that a statistic is out of date during, it will update it. If it is executing in synchronous mode, it puts a hold on execution plan compile. So you will see [Plan Guide] blocking resource description in sys.dm_os_waiting_tasks. If you are not seeing any blocking because of that, turning on auto update statistics asynchronous is not beneficial.
A reason to turn off auto update statistics might be you are trying to minimize recompile events, you don’t want SQL Server to take a sample of the data, or you don’t want to cause performance impacts in middle of day. Also the auto update statics fires when 20% of the data changes and must have at least 500 rows. Therefore many large tables might not ever get statistics update, so Job is still required.
This is a Performance setting not a HA setting.
PS The 20% threshold setting changed with Trace Flag, T2371, which decides its update decision based on the size of the table instead.
Hi
How i am cheking to reduce downtime ???