4

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?

3 Answers 3

8

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.

3
  • Point of clarification: The "20%" isn't only when the table grows (INSERTs), its also when existing rows are modified (UPDATEs).
    – BradC
    Jun 24, 2010 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/… Oct 29, 2010 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, 2010 at 16:48
0

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.

0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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