Take the 2-minute tour ×
Server Fault is a question and answer site for system and network administrators. It's 100% free, no registration required.

Is it necessary to manually UPDATE STATISTICS for a SQL Server database regularly (e.g. daily) when *AUTO_CREATE_STATISTICS* and *AUTO_UPDATE_STATISTICS* options are ON?

share|improve this question

3 Answers 3

up vote 7 down vote accepted

Yes you need to update the statistics manually, especially as your database gets bigger.

The auto_update statistics logic only kicks in after the table has grown by 20% from what it was the last time the statistics were updated. In larger tables this can be hundreds of millions of records. In these cases the statistics will become out of date before the auto update logic kicks in so you'll have to update them manually.

Now if you rebuild your indexes on a regular basis, then the stats for the index would already be updated as this is done as part of the index rebuild process. If you defrag your indexes instead of rebuilding them then you will need to update statistics as the updating of statistics is not done automatically when you defrag the index.

share|improve this answer
    
Point of clarification: The "20%" isn't only when the table grows (INSERTs), its also when existing rows are modified (UPDATEs). –  BradC Jun 24 '10 at 15:17
    
please note that Column statistics are NOT updated during the index rebuild and should be done at that time --> rodcolledge.com/rod_colledge/2009/07/… –  Eric Labashosky Oct 29 '10 at 12:49
    
I've added clarification that the stats which are automatically updated when you do a rebuild are the stats on the indexes. –  mrdenny Oct 29 '10 at 16:48

Recommended practice is to have the statistics updated every night as part of a maintenance plan. If you don't do that, and leave those two options off, then statistics are not updated and query performance will suffer.

share|improve this answer

Blogger BenchmarkIT tested the "20% rule" in his post here.

Primary finding was that it does appear to update statistics on the clustered index's stats when more than 20% of the table is changed, but it doesn't appear to auto-update stats associated with other indexes on the same table.

His earlier blog post here looks at what happens when you reindex your database. It shows that rebuilding an index does update stats for that index, but any "stand-alone" statistics that aren't connected with an index don't get updated.

Both of these add more support to the "Yes, you need to manually update stats" argument.

share|improve this answer

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.