We had an issue earlier on in the week whereby a query had horribly regressed and as a result, it had completely taken over the SQL instance (OLTP Server) as it is frequently executed. One of the outcomes of the incident was that we were to consider disabling AUTO_UPDATE_STATISTICS, allowing for the the overnight maintenance jobs to handle statistics updates, and keep stats static during the day. The arguement for disabling statistics updates is that it will ensure that we retain the same consistent plans. By default my preference is not to do that, and more proactively monitor memory grants, and memory used, using query store and/or extended events, and to alert when significant issues occur.
I had looked around and couldn't really find any instances where people had blogged/posted about disabling this option and the sucesses of it. My question is are the any noted instances that people have of success of disabling statistics/ please can people relay successful implementations of doing so?
SQL Version: 2016 Standard EE
Best Answer
There are valid reasons for disabling AUTO_UPDATE_STATISTICS, such as wanting to have more control over the updating process of your stats. However disabling the auto update to prevent plans from changing destroys the purpose of the optimizer.
If you know your data very well and you are confident that a given plan will lead to good results regardless of the growth in certain tables, you should just force the plan with query store or a plan guide (https://docs.microsoft.com/en-us/sql/relational-databases/performance/plan-guides?view=sql-server-ver15). However before jumping into these kind of solution you should try the more standard approach of adding indexes, rewriting queries, adding hints, etc.
Avoid "shotgun" approaches such as disabling auto_update_statistics or setting MAXDOP =1 for whole instances. Instead you should identify the specific queries that are becoming pain points and analyze their executions. Contained "patches" such as index creation, hints or even forcing plans on specific queries should always be preferred over changes in configuration that will affect your workloads all across the board.
I hope this helps.