Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.
Sign up to join this community
I just learned that a client company I work for has decided to keep the auto update statistics options off for some of their SQL Servers, and the DBAs manually troubleshooting performance issues when they arise.
However, this kind of does of not make sense to me. Why would you want to prevent the statistics from being updated?
My advice:
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.
In the days of old (SQL Server 2000) having the auto-update stats setting on could result in large "pauses" in OLTP applications when SQL decided to do a statistics update.
From SQL Server 2005 onwards there is an asynchronous option which will not result in the "pause" when stats are out of date and subsequently recompiled. The stats will be recompiled asynchronously for the next time they are required.
Bear in mind though that there is a balance to be had - for large data sets you may want the current query to take the most up to date statistics into account, since the query may take exponentially longer with an inefficient query plan.
You can also monitor the number of recompiles per second and other crucial performance metrics using the perfmon counters that are published by SQL Server, as they say, the proof is in the pudding.
How active are the systems?
If they are mostly read systems you might get by without updating, if they happen to do that manually when they are changing data (insert, update, delete).
However for best practice it is advised to be kept on cause I think it would be rare for it to be a performance issue. I would probably send them articles and blog post suggesting that it be left on. Kimberly Tripp has some good ones.
While I will never contradict nor intentionally dis MrDenny in any way (too much respect for his knowledge and contributions to the community) I've noticed the MS documentation recommending turning auto update stats off.
At Storage and SQL Server capacity planning and configuration (SharePoint Server 2010) MS recommends setting it off.
I've also read the same recommendation by MS for earlier versions of SP but can't find the links just now.
But I'm NOT a SP Expert DBA so you might also want to check what Chun Liu, MS Permier Field Engineer, has to say about it at Chun Liu on SharePoint or just ignore this post and leave it on.
You typically only turn them off if you have a method of updating them yourself. I do this all the time since we need better or just different sampling than the auto-stats will do. That is fairly common but you would not simply turn it off and do nothing else.
