AUTO_UPDATE_STATISTICS and AUTO_UPDATE_STATISTICS_ASYNC August 22, 2007Posted by furrukhbaig in Indexes, Performance, SQL Server 2005.
Tags: AUTO_UPDATE_STATISTICS, AUTO_UPDATE_STATISTICS_ASYNC, execution plan, sql server statistics
Many of us are familiar with the AUTO_UPDATE_STATISTICS database option. It provides the Optimizer with up-to-date index and column statistics to achieve optimum query plans queries execution.
Everything comes up with a price. There are some pros and cons of using that option and choice should be made very carefully otherwise it can seriously hurt performance. For example, the AUTO_UPDATE_STATISTICS option, when turned on (which it is by default for a new database), tells the Query Optimizer to automatically update the index and column statistics as data changes. It is vital because data changes can affect Optimizer decision in selecting optimum query plan. Statistics are automatically updated when the statistics used in a query execution plan are outdated. Statistics are considered outdated when 20% or more rows in a table have changed.
So what it means to me is when Optimizer consider statistics need to be updated it does it right there and then recompile the query to use updated statistics and create new execution plan and execute the query. Most of the time this process is not noticeable but in some scenarios it can hinder query performance and might cause query timeouts.
For large tables that have many indexes and statistics on columns it can take significant amount of time. Query has to wait during that time to be recompiled using new statistics and then time to execute that query plan. This can cause serious delay and can happen on periodic bases for example query usually takes 2 second but some time it takes more the 10 seconds. And believe me it will take you some time before realizing that what’s going on.
Another point to consider is it is unlikely that up-to-date statistics will change execution plan as queries should be written in a manner so they can make use of available indexes and cached for reuse. I would not expect my stored procedure to keep changing its execution plan because it can hinder performance and make no use of cached execution plan. But in some cases, with dynamic SQL, this will help improve performance. So use it with care.
The new feature in 2005 AUTO_UPDATE_STATISTICS_ASYNC can be used to avoid query wait for updated statistics. It enables optimizer to carry on using old statistics while statistics are updated asynchronously and made available for next execution. Keep in mind this can also make queries slower as it uses old statistics without any wait and there is a possibility that changes in data was significant and optimizer should have updated statistics to get optimal execution plan. But in most cases this should work fine. As I mentioned before that its unlikely that execution plan changes for regularly running queries unless columns in ORDER BY and WHERE clause keep changing.
So Recommendation is to use both options ON to be on safer side but always test this with workload in your scenario.