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.
Chad Boyd
~~~
This posting is provided "AS IS" with no warranties, and confers no rights. Use of any included script samples are subject to the terms specified at http://www.mssqltips.com/disclaimer.asp and http://www.mssqltips.com/copyright.asp.
About Chad Boyd
Chad is an Architect, Administrator, and Developer with technologies such as Sql Server (and all related technologies), Windows Server, and Windows Clustering. He currently works as an independent consultant and also spends a significant amount of time writing, talking, presenting and blogging about Sql Server in person and online at http://mssqltips.com. In the past, Chad has worked with companies and organizations such as Microsoft Corporation and The American Red Cross, and provided consulting/support services at companies such as Bank of America, HP, Citigroup, Qualcomm, Scottrade, TJX, SunTrust, and Zurich Financial Services. For over 3 years with Microsoft Corporation Chad was responsible for providing onsite and remote support, guidance, and advice with SQL Server products to some of Microsoft’s foremost enterprise customers running the largest, most complex SQL Server installations and configurations in the world. This included all SQL Server products and versions, including SQL Server 7.0, 2000, 2005, and recently 2008, the SQL Server database engine, Reporting Services, SSIS/DTS, Notification Services, and Analysis Services on both 32 and 64 bit systems. Chad's primary responsibilities today include troubleshooting critical server situations, performance tuning and monitoring, disaster recovery planning and execution, architectural guidance for new Sql Server related deployments, and delivering deep technical workshops/presentations/proof-of-concept sessions covering a variety of technologies and functionality. Chad regularly posts Sql Server related content, tools, and advice with the mssqltips team at http://blogs.mssqltips.com/blogs and http://mssqltips.com. Chad can be contacted via his blog or email at chad dot boyd dot tips at gmail dot com.
