Learn more about SQL Server tools



solving sql server problems for millions of dbas and developers since 2006 join MSSQLTips for free SQL Server tips
































Top SQL Server Tools



































   Got a SQL tip?
            We want to know!

AUTO_UPDATE_STATISTICS_ASYNC SQL Server Database Configuration

MSSQLTips author Chad Boyd By:   |   Read Comments   |   Related Tips: More > 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



Last Update: 3/6/2007


About the author
MSSQLTips author Chad Boyd
Chad Boyd is an Architect, Administrator and Developer with technologies such as SQL Server, .NET, and Windows Server.

View all my tips
Related Resources


print tip Print  
Become a paid author





join MSSQLTips for free SQL Server tips     



Comments and Feedback:

Post a Comment or Question

Keep it clean and stay on the subject or we may delete your comment.
Your email address is not published. Required fields are marked with an asterisk (*)

*Name   *Email Notify for updates



Comments
Get free SQL tips:

*Enter Code refresh code


 
Sponsor Information







Copyright (c) 2006-2014 Edgewood Solutions, LLC All rights reserved
privacy | disclaimer | copyright | advertise | about
authors | contribute | feedback | giveaways | free t-shirt | user groups | community | events | first timer?
Some names and products listed are the registered trademarks of their respective owners.