How does AUTO_UPDATE_STATISTICS_ASYNC work with Sql 2005?


By:   |   Updated: 2007-10-15   |   Comments   |   Related: More > Database Administration


A customer this week inquired about auto updating of statistics in Sql 2005, particularly with regards to how and what the new AUTO_UPDATE_STATISTICS_ASYNC option is and how it works...well, here you have it...

With Sql Server 2005, the AUTO_UPDATE_STATISTICS_ASYNC option configures a given database to update statistics asynchronously vs. synchronously (as occurs without the option enabled, or in Sql 2000 as well). Typically, if a given query request triggers an auto stat updating event without this option set, the query will wait as stats are updated, then the query will be executed. If you set this option however, the query will be executed against the old/existing stats, while submitting a request of sorts in the background telling the engine to update the stats automatically as soon as possible, without holding up the existing query request(s). As soon as the background operation completes, new query requests will begin to use the new statistics information. One thing to note in particular is that this option only comes in to play for auto updating of stats, not manual updating or on-demand updating on request by a user.

In many scenarios, this option can be enabled with little risk to negative side-effects that stale-statistics can often cause (poor query plans for example)...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.



Last Updated: 2007-10-15


get scripts

next tip button



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





Comments For This Article





download





Recommended Reading

How to rename a SQL Server database

How to determine SQL Server database transaction log usage

Different ways to determine free space for SQL Server databases and database files

How to read the SQL Server Database Transaction Log

Execute SQL Server Script Files with the sqlcmd Utility








get free sql tips
agree to terms


Learn more about SQL Server tools