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?
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.Sign up to join this community
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.