Rebuilding Indexes or Updating Statistics, which do you do first?
Why?
Are there differences?
Rebuilding an index will update statistics with the equivalent of a full scan - doesn't matter whether you use DBCC DBREINDEX or ALTER INDEX...REBUILD, the effect is the same. It can do this because it sees a complete picture of the index while it’s doing the rebuild.
Reorganizing an index (using the old DBCC INDEXDEFRAG, or the new ALTER INDEX...REORGANIZE) will NOT update statistics at all, because it only sees a few pages of the index at a time.
So, which is the correct sequence?
- If your default for updating statistics is to do a sampled scan, then having rebuild update the statistics with a full scan (as a side effect), and then proceeding to manually update them again with the default sampled scan, means that after both operations you're left with sampled statistics. You've wasted resources doing the sampled scan AND you've lost the 'free' full-scan statistics that the index rebuild did for you.
- If your default is to do a full scan, then you don't lose out on having the best statistics, but you do waste resources by unnecessarily updating statistics a second time.
So what's the solution?
The simple answer is not to update statistics on indexes that have just been rebuilt.
The more complicated answer is to:
- Have a list of indexes (F) that you know will cause workload performance problems if they get fragmented
- Have a list of indexes (S) that you know will cause workload performance problems if the statistics don't get regularly updated
- Evaluate fragmentation for list (F), and choose to reorganize, rebuild, or do nothing
- For all indexes in list (S) that were not rebuilt in step 3, update statistics
Reference:
Paul S. Randal
http://www.sqlskills.com/blogs/paul/post/Search-Engine-QA-10-Rebuilding-Indexes-and-Updating-Statistics.aspx
| Next > |
|---|
