Rebuilding an index does two things. First, since you’re literally rebuilding the index, it reorders the pages and the rows on those pages. This has a benefit in that it can eliminate some pages, making the index smaller and condensing the rows to pages, based on your fill factor. Second, as a part of the rebuild, you get a full statistics update.
What happens is, people rebuild the indexes because they think that it’s necessary to constantly and continually defrag the indexes. And they see performance enhancements… usually short term ones. Most of these come from the new set of statistics that were generated, showing more accurate row counts, leading the optimizer to make better choices for the plan. A few may result from queries that are doing massive scans having fewer pages to scan, improving performance, in the short term until the indexes fragment again.
In short, the performance enhancements from index rebuilds are frequently an illusion that it comes from the index rebuild. Instead, it’s because of the statistics update. So, many people, just skip the index rebuild and instead update the statistics. More often than not, and I mean, 90%+, this is the biggest contributor to improved performance.
Comments