Take the 2-minute tour ×
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's 100% free, no registration required.

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?

share|improve this question
add comment

6 Answers

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.

share|improve this answer
add comment

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.

share|improve this answer
add comment

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...

share|improve this answer
    
Is it maybe a legacy issue? Was stats maintenance different in 2k? –  JNK Dec 2 '11 at 21:04
    
@JNK: IIRC some changes to the row change threshold but I'd have to look it up... ah yes, now tracked per column not per table msdn.microsoft.com/en-us/library/ms190283.aspx –  gbn Dec 2 '11 at 21:06
    
Is it 20% in 2008? –  JNK Dec 2 '11 at 21:08
    
    
20%+500 for >500 per column since SQL Server 2005+ –  gbn Dec 2 '11 at 21:11
add comment

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.

share|improve this answer
add comment

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.

share|improve this answer
add comment

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.

share|improve this answer
4  
Your advice is specific to SharePoint databases, it's not generally applicable to all SQL Server installs. –  Simon Righarts Dec 3 '11 at 9:24
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.