Why set Auto Update Statistics to False?

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.

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.

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.