Tuesday, November 13, 2007

Update statistics before or after my index rebuild?

I already posted some information about this some time ago and now I finally have some confirmation from a real guru about this often asked question.

Q4) In a maintenance plan, is it a good idea to do an index rebuild followed by an update statistics?

A4) No! An index rebuild will do the equivalent of an update stats with a full scan. A manual update stats will use whichever sampling rate was set for that particular set of statistics. So - not only does doing an update stats after an index rebuild waste resources, you may actually end of with a worse set of stats if the manualy update stats only does a sampled scan

So to wrap it up, a reindex updates the statistics with a full scan because he has to read through the entire index anyway. It does however not update non-index statistics so therefore you might want to trigger an update anyway. The nice thing about SQL Server 2005 is that he will only update the statistics if it is necessary if you use sp_updatestats.

A little test script to demonstrate some of these points.