AUTO_UPDATE_STATISTICS_ASYNC ON?

Question

I have been advised that setting the AUTO_UPDATE_STATISTICS_ASYNC ON may be a good idea. I have been reading a couple of ideas and still not sure as this is a non default setting.

Any further advice?

Answer 1

This is a big "It Depends..." but generally speaking you don't want to have statistics updating asynchronously for most workloads because it can mean that you generate a less-than ideal execution plan based on the existing out-of-date statistics.

If you have a scenario where the auto update stats execution takes an excessive amount of time and causes timeouts, you might consider going the route of letting stats update asynchronously so that it doesn't create that problem. The few seconds it typically takes to auto update statistics can easily result in minutes of difference in execution times from a better execution plan from the new statistics.