Why set Auto Update Statistics to False?

Question

I've just inherited about 20 instances of SQL Server, as part of a wider acquisition project. I'm in the process of assessing performance and I don't like the way maintenance plans have been implemented.

I'm seeing daily blanket index rebuilds (I can deal with this one) and also daily manual updating of statistics.

Around half of the databases have been set to Auto Update Statistics = False, for reasons which are not clear other than I am told it is to reduce 'Performance Issues'...

I always thought, and worked to, best practice of setting this to True and felt the Manual Update was not necessary if this setting was True. Am I wrong?

Can anyone explain what the benefit would be in having this set as False, but doing a daily manual update instead?

I should mention that some of the databases are highly transactional (millions of Inserts, Deletes, Updates per day) Others are low in terms of transaction rates, and some are all but read-only. There is no rhyme or reason though as to which have the Auto Update setting set to False. It appears to be a lottery.

Answer 1

You are correct, I also believe that in most cases the Auto Update statistics should be set to true we should allow SQL Server to decide when to update stats and believe me it does good job. When this is set to true it make sure stats are updated about distribution of data in the field which would eventually help optimizer to prepare better plan. The important thing to note here is Auto update stats fire when 20% of data changes in table. So you should not feel that on a table with 100K rows if 10 rows are updated then status update will fire.

A more deeper analysis is done by Paul Randal in the blog Understanding When Statistics Will Automatically Update. I have not seen any drawback if this option is set to true. Yes you can see some I/O activity when this option is set to true.

Important conclusion which one can draw from the blog is

Even if a statistic becomes outdated as the result of a modification, it will not automatically update after the modification completes. The statistic will automatically update the next time a query plan uses it.

For cases where you have just read only databases or databases where you just do select operation and there is no DML operation, in that case you can keep the option to false but again no harm would come if you keep it true. We mostly see database with certain amount of activity.

Answer 2

This is too long for a comment so I'll chime in with another case where one might want to turn off auto update stats. I've worked with databases supporting high-volume OLTP workloads and a stringent query performance SLA in milliseconds. Nearly all queries were trivial with a lot of attention to query and index tuning detail and some of the tables were quite large. There wasn't much value in updating stats during peak periods in this situation and auto-update stats would violate the SLA. Consequently, maintenance was done during non-peak periods via a scheduled job.

Another option is to turn on both AUTO_UPDATE_STATISTICS and AUTO_UPDATE_STATISTICS_ASYNC database options. This will allow queries to proceed with execution plans based on stale statistics rather than incur the overhead of updating stats synchronously. This is especially appropriate for an OLTP workload as long as the server is sized to accommodate the query workload plus the background stats update.

Answer 3

Generally I would say that having auto update statistics on is beneficial. But like any setting, there are reasons you can turn it on or off.

One is that some tables have a lot of churn, and perhaps queries are not very sensitive to accurate statistics. Think ETL or other bulk scenarios where you're changing a lot of data, but either not reading it from there, or not reading it much. There's not much point to having auto statistics updates kick in and cause a bunch of I/O to provide more accurate stats that won't ever be used.

You also might have scenarios where you update data multiple times throughout the day, but don't necessarily want to update statistics after every update. (Say the data is only queried during certain hours of the day - no need to update the stats multiple times when the data won't be queried in the meantime anyway.)

Or maybe you just have a write-heavy workload. Or the reads are generally full scans, where statistics are not extremely important.

Answer 4

Auto stats updates can have horrible results on large tables due to the algorithm used by MS to determine sample rate, even with the improvements in this altorithm since 2016 +. It is a sliding scale algorithm and I believe, if you check your stats on large tables in scenario where auto stats update is turned on, you are going to find extremely low sample rates. Use DBCC to spot-check your last stats updates and I think you are going to find surprising results. Particularly in scenarios where queries are running fine for some time (days/months) and suddenly you start to get calls re long running ops. On DB's with large tables and high churn, I normally turn off auto update stats and run maint jobs to handle it in off hours. I prefer TigerBox because you can control maint ops on indexes by number of pages in index, thereby creating separate jobs based on index/table size. If you don't want to go to these lengths, on problematic tables/indexes, run a manual stats update using a sample rate appropriate for that table and use the persistent=yes option - this will cause auto stats update to use your persistent sample rate rather than letting the algorithm have it's way with you.