By: Chad Boyd | Updated: 2007-03-06 | Comments | Related: > Maintenance
Problem
Updating Statistics is critical for the SQL Server optimizer to have the latest information about the database to select the best query plan. Unfortunately, I am hesitant to enable AUTO UPDATE STATISTICS at a database level or for specific tables. I am concerned that this command could run during core business hours or during a big batch operation. I also have limited time to perform this maintenance as well as regularly scheduled maintenance. Does SQL Server 2005 offer any new features over SQL Server 2000 that will give me the confidence to enable AUTO UPDATE STATISTICS at a database level? Can I rest assured that my queries will not get blocked and my performance will not be impacted when the statistics are updated?
Solution
With SQL Server 2005, the AUTO_UPDATE_STATISTICS_ASYNC database configuration option configures a given database to update statistics asynchronously vs. synchronously. Updating statistics synchronously is the default behavior in both SQL Server 2000 and 2005. Typically, if a given query request triggers an AUTO UPDATE STATISTICS event without this option set, the query will wait as statistics are updated. Once the statistics are updated, then the query will be executed. However, if you set this option (AUTO_UPDATE_STATISTICS_ASYNC) the query will be executed against the existing statistics. At the same time, while a background request will tell the database engine to update the statistics automatically as soon as possible, without preventing any query request(s). As soon as the background operation completes, new query requests will begin to use the new statistics information. One important note is that this option only comes in to play for AUTO UPDATE STATISTICS events, not manual updating or on-demand updating of the statistics on request by a user.
Example AUTO_UPDATE_STATISTICS_ASYNC Code
| sp_helpdb YourDatabaseName GO -- Must have AUTO_UPDATE_STATISTICS enabled first ALTER DATABASE YourDatabaseName SET AUTO_UPDATE_STATISTICS ON GO ALTER DATABASE YourDatabaseName SET AUTO_UPDATE_STATISTICS_ASYNC ON GO sp_helpdb YourDatabaseName GO |
AUTO_UPDATE_STATISTICS_ASYNC Benefits
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. poor query plans. 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 seeing nothing but improved throughput with this option enabled.
AUTO_UPDATE_STATISTICS_ASYNC Caveats
It is necessary to have AUTO_UPDATE_STATISTICS enabled first before enabling AUTO_UPDATE_STATISTICS_ASYNC because AUTO_UPDATE_STATISTICS_ASYNC is dependent on AUTO_UPDATE_STATISTICS.
In addition, Adam Machanic of the MSSQLTips.com community indicated that the AUTO_UPDATE_STATISTICS_ASYNC may be a favorable configuration for OLTP environments and may not be for data warehouse environments. The specific scenario is related to databases that have a lot of data modification throughout the day. He reports that he has observed the AUTO_UPDATE_STATISTICS_ASYNC configuration cause major issues in two data warehouse systems that involved near-real time loads. In both cases, a similar pattern occurred: queries against a recent time frame hit just after the load, did not trigger a synchronous update and the query optimizer chose a seek where a scan would have been more appropriate. This is due to the increased data density in that range. The bottom was really bad performance in both cases until AUTO_UPDATE_STATISTICS_ASYNC was disabled.
As with all SQL Server configurations and application scenarios, it is necessary to test the changes and validate the advantages and disadvantages based on your application and environment.
Next Steps
- Evaluate how statistics are updated for your production databases and determine if the AUTO_UPDATE_STATISTICS_ASYNC database configuration would be beneficial to these environments.
- If you are not updating statistics in either your SQL Server 2000 or 2005 environments, let this tip serve as a reminder that the statistics are valuable to your overall SQL Server health and performance.
- Check these additional references:
- Check out the following related MSSQLTips.com:
- Special thanks to Chad Boyd of the MSSQLTips.com community for this tip.
About the author
Chad Boyd is an Architect, Administrator and Developer with technologies such as SQL Server, .NET, and Windows Server.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
Article Last Updated: 2007-03-06
