10

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.

6

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.

10

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.

  • I was trying to think of an example where auto_update_stats would actually cause problems and this is a great one - I'd upvote it twice (if I could) for the excellent work-around as well, avoiding the normal stats delay that would accompany a query – SqlRyan May 4 '16 at 18:03
  • 1
    I have had situations with larger databases (VLDB), that the auto_update stats option is ON and SQL would kick off during inopportune times of the work day. I turned it off and had to be more strategic about manual updates to specific tables and stats, instead of letting the server determine the tables and when. This made my system more predictable, but at a higher management cost (no doubt), but needed to happen to avoid intruding update tasks. If "blanketing" the system with typical index/stats management is your thing, leave it on. Otherwise, some situations may required detailed strategy. – SnapJag Apr 4 '17 at 21:52
6

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.

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.