Reasons for disabling statistics auto update?
My advice:
- Leave 'auto update stats' on (until you run into a very good reason not to) - you don't want a big delete in middle of a day to throw off query plans until the next time you run maintenance.
- However, schedule index maintenance/update statistics at a quiet time.
sp_updatestats
will update all stats for all tables in a database for you, but that will also cause stored proc recompiles, so choose your time carefully (i.e.sp_updatestats
right before your end-of-month reporting is probably suboptimal).
How often you need to run index maintenance/rebuild stats depends on your database load, specifically how often your data is modified (i.e. INSERT
/UPDATE
/DELETE
). If you're modifying data all over the show (i.e. a staging table for a weekly batch process), you probably want to update stats/reorganize indexes nightly. If your data is rather more static you can probably make it a weekly or fortnightly schedule.
I've only seen it once with an bad app that had badly indexed heaps and had heavy ETL.
This was rubbish and luckily not mine.
Otherwise, there is no reason.
If you are getting statistics updates at inappropriate times then it means you are doing incorrect index/stats maintenance or have massive deletes/loads that hit the threshold.
With SQL Server 2005+ you can defer the stats update anyway.
See "When to Use Synchronous or Asynchronous Statistics Updates"
It'd be interesting to see what article they've followed or read about to make this choice...
The only time I've turned off auto-stats was when I used some undocumented commands to create some fake stats which I didn't want the system to wipe out as data was being loaded. This was a VERY edge case.