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.

In SQL Server, statistics are updated automatically when Auto Update Statistics in True (which is the default). Is there a reason to update statistics manually and in what circumstances?

share|improve this question
add comment

3 Answers 3

up vote 16 down vote accepted

Sure, if your data is changing more frequently than the rate of auto stats (or, say, you are updating < 20% of the rows frequently, like updating statuses or date/time stamps). Or if your table is huge and it is not changing enough to trigger auto stats updates. Or if you have filtered indexes (since the auto stats threshold is still based on the % of change on the whole table, not the % of change of the subset of rows in the filtered index).

share|improve this answer
    
+1 for filtered indexes. I think that's the main reason, or on VERY large data sets. I have tables that I insert 30m rows a month, but it's a small percentage so stats don't normally get updated unless I kick it off manually. –  JNK Feb 10 '12 at 20:25
    
+1 Also, you do it on a schedule to reset the counters, means less chance outside your maintenance window. –  gbn Feb 11 '12 at 14:37
add comment

A couple more (superficial) reasons:

Auto-update stats will block the query that triggered the update until the new statistics are ready.

...Unless you also enable auto-update stats asynchronously. Then the query that triggered the update won't wait for the new stats, but will potentially run with the old, incorrect stats.

I also ran into some strange blocking issues when a database with some relatively large tables (44M rows, 8.5 GB) would start updating stats. We decommissioned that application before I was able to really track down what was going on, though.

share|improve this answer
    
+1 That's a great point about the blocking and the async option. –  Aaron Bertrand Feb 10 '12 at 21:15
add comment

Yes, remember that auto stats are always generated with a default sampling rate. That default sampling rate may not accurately produce statistics that represent your data.

http://sqlblog.com/blogs/elisabeth_redei/archive/2009/03/01/lies-damned-lies-and-statistics-part-i.aspx

share|improve this answer
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.