Auto Update Statistics Asynch - Are you really, really using it?
Hello all,
It’s been awhile since I’ve updated the blog from my part. It’s been a rough month, and I’ve been on vacation prior to that, but I’m back. 
What I want to get your attention in this post is to the “Auto Update Statistics Asynchronously” database option. Not to discuss in which scenarios to use it, even because Microsoft does issue a set of recommendations for it in the MSDN article on Using Statistics to Improve Query Performance, but to call your attention on something I’ve seen in the field quite often and has led to some confusion on the matter.
To use the “Auto Update Statistics Asynchronously” database option that has been around since SQL Server 2005, “Auto Update Statistics” must also be ON for the first to work. As you know, “Auto Update Statistics” allows the query optimizer to update out-of-date statistics, and it does so in a synchronous fashion when a query is executed. What “Auto Update Statistics Asynchronously” essentially does is to allow this update to occur after the query is executed with the current statistics that have been identified as being out-of-date.
Now the issue I’ve found on the field is that sometimes DBAs set the “Auto Update Statistics Asynchronously” ON without “Auto Update Statistics” being ON.
I’ve seen one of two reasons for this:
- Either because they had previously turned off “Auto Update Statistics“ on a database but now they need them to auto update asynchronously.
- Or they mistakenly set “Auto Update Statistics” OFF before enabling “Auto Update Statistics Asynchronously”.
Not only does Management Studio allow it, but it doesn’t issue any sort of warning that you might not be getting the configuration you are aiming for, even on Denali CTP3.
This is not a GUI issue, because if you look at sys.databases, the operation done in SSMS is valid:
And the same goes if you run the T-SQL commands themselves, no word of warning:
Going back to sys.databases:
So there you have, if using the “Auto Update Statistics Asynchronously” database option, be sure to have the “Auto Update Statistics” database option enabled, or it will simply do nothing.
EDIT: I've openned a Connect item. Please click here if you would like to cast your vote.
Until next time!
Disclaimer: I hope that the information on these pages is valuable to you. Your use of the information contained in these pages, however, is at your sole risk. All information on these pages is provided "as -is", without any warranty, whether express or implied, of its accuracy, completeness, fitness for a particular purpose, title or non-infringement, and none of the third-party products or information mentioned in the work are authored, recommended, supported or guaranteed by Ezequiel. Further, Ezequiel shall not be liable for any damages you may sustain by using this information, whether direct, indirect, special, incidental or consequential, even if it has been advised of the possibility of such damages.