Auto Update Stats Async Enabled
Blitz Result: Auto Update Stats Async Enabled
SQL Server maintains statistics that help it decide how to build better execution plans. It periodically reviews the content of tables, updates its statistics about the distribution of data in fields, and uses that for execution plans. By default, this happens automatically – statistics are updated when about 20% of the data in a table changes – and it happens whenever a query needs to read data from that table. This part of our SQL Server sp_Blitz script checks sys.databases looking for databases where auto-update-stats-async has been turned on, meaning SQL Server will go ahead and build an execution plan with the out-of-date statistics and then update statistics in the background to help the next query’s performance.
This setting is up for debate in the SQL Server community. We recommend setting it back to the default (disabled) unless you’ve got evidence that delays caused by statistics updates are causing you problems. There are opposing viewpoints, though, and here’s a few links for more learning:
- Tara Kizer’s opinion on auto_update_stats_async
- Microsoft KB post: memory leak if you enable auto_update_statistics_async
- Erik Darling thinks everyone is wrong
To Fix the Problem
In SQL Server Management Studio, you can right-click on each database and go into its properties to change Auto Update Stats Asynchronously back to the default, Disabled. Auto Update Stats also has to be enabled for this change to actually do anything – otherwise stats aren’t being updated anyway.
After the change, you can monitor overall system performance looking for improvements, especially around query execution plans.
11 Comments.
I was running sp_Blitz and noticed that it states that my databases have auto-update-stats-async disabled even though they are enabled (check both my policies and manually verified), so I check the proc, and it appears that line 425 is incorrect.
It reads ” WHERE is_auto_update_stats_async_on = 1 ;”
should it not be
” WHERE is_auto_update_stats_async_on = 0 ;”
John – actually, we are looking for is_auto_update_stats_async_on = 1. Turning that setting on is a problem.
Brent,
I think I am missing something (trying not to be thick-headed), per the “To Fix the Problem” it states, “In SQL Server Management Studio, you can right-click on each database and go into its properties to change Auto Update Stats Asynchronously to Enabled.” which seems to be contradictory to the setting that you are looking for, and also conflicts with the blog you have linked to learn more.
Again, unless I am missing something it seems like all the information provided states to turn this option “ON”, but the blitz proc is looking for where it is on and warning to turn it off.
Thanks for your patience and help in walking me through this. Glenn Berry was the original person that told me to turn it on, so I have made it part of the database policies to check that it is…
John – AH, gotcha, that’s where the real problem is – the page should have said “go into its properties to change Auto Update Stats Asynchronously to Disabled.” I’ve corrected that – by default, that option is off, and it should stay off unless you have good reason to change it. Glenn may have had good reasons for your specific scenario, but this isn’t something that should be changed without a reason. Thanks!
Ok thanks for clearing up the confusion, just fyi, the blog post above seems to lean towards turning it on, also…
Thanks again
In the Fix the Problem section above it states the following:
This change requires that the database be in single-user mode.
I was able to change the Auto Update Statistics Asynchronously setting to false without putting the db in single user mode.
Am I missing something?
thanks
Tim – you’re absolutely right! I goofed on that and I’ll fix it in the post – I copy/pasted that from another one of the Blitz Result pages. Sorry about that!
No Worries. Thanks so much for this sp_blitz. Its helping me quite a bit.
The problem is the problem message says “disabled” when it should say “enabled”.
Brent,
I disagree with this advice. Just because stats can be updated doesn’t mean that they are bad stats. Also, even if the stats are collected again it doesn’t mean that the new stats will result in a different execution plan – especially if you are using parametrized queries instead of a purely ad-hoc workload.
With this in mind, disabling async stats would have the execution of your query wait until new stats could be gathered. Many times this won’t result in a new execution plan, so you have waited for new stats for no gain at all. Enabling async prevents this wait and potential slowdown.
Sure, there may be downside to this setting on some systems, but I would lean more towards having this enabled than disabled. I certainly wouldn’t flag this as a performance risk.
-js
JS – I disagree with your disagreement. 😉
I understand that just because stats can’t be updated doesn’t mean they’re bad – but it means there’s a chance they’re bad, especially in a scenario where we’ve got a large amount of data that hasn’t been updated in a long time.
When you say “many times this won’t result in a new execution plan,” I’d be curious to hear if you’ve done any studies on that or can point to any evidence. I understand that in some scenarios, it won’t EVER result in a new execution plan – but that doesn’t mean that’s the case for ALL scenarios.
sp_Blitz is about helping you flag non-default settings that may not be a good idea in your environment. I’m not a big fan of changing SQL Server settings away from the defaults without a good reason. Here’s a post I wrote about that:
https://www.brentozar.com/archive/2011/12/consulting-lines-pilot-dog/
Thanks for the feedback, though!