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

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 Mar 5 at 15:43
  • Apologies, this is for an OLTP server. I should have mentioned that within my question, I shall edit it now. – KrishnP92 Mar 5 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 Mar 5 at 16:28
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://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.

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 Mar 5 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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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