1

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 enter image description here

Improve this question
3
  • Which version are you using ? Is your system OLTP or OLAP ? Are you adding new data during the day ? You will get better advice if you can answer these questions
    – Stephen Morris - Mo64
    Commented Mar 5, 2021 at 15:43
  • Apologies, this is for an OLTP server. I should have mentioned that within my question, I shall edit it now.
    – KrishnP92
    Commented Mar 5, 2021 at 15:51
  • I have successful experience of disabling the option on extremely high loaded OLTP system. But that was done to get more precise statistics using own algorithm to determine which statistics have to be updated. I doubt disabling the option to keep plans is a good idea. You could keep query plans for some particular queries by forcing query execution plan, using glan guides or query hints.
    – Denis Rubashkin
    Commented Mar 5, 2021 at 16:28

2 Answers 2

Reset to default
2

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://learn.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.

Improve this answer
1
  • Hi Fernando, thaks for your response. My thoughts are exactly that. I completely agree that there are valid cases for it, although I'm not sure it's really suited for our instance . As has been mentioned my yourself and others, I think the better option for us is to force plans after we have better monitoring for memory grants.
    – KrishnP92
    Commented Mar 5, 2021 at 17:59
0

Updating statistics on a schedule instead of using AUTO_UPDATE_STATISTICS works reasonably well for many databases. But I think its an overreaction here. Instead turn on Query Store and monitor for bad plans and other expensive queries.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.