Is It a Bad Idea to Rebuild All Indexes Every Night?

Q: Our index maintenance plan is about as simple as it gets: We rebuild all of our indexes every night. The problem is that it’s taking longer and longer to complete, and the log backups are huge every morning. What can we do better?

Learn more from "Don't Forget about Backup Compression" and "Performance Tip: Find Your Most Expensive Queries."

A: You’re not alone. Many people have a simple index maintenance plan such as yours and are finding that as data volumes increase, and maintenance windows start to close or disappear, the simple rebuild-everything-every-night plan isn’t acceptable anymore.

This plan becomes a problem because an index rebuild operation will always build a new index, which means extra space is required to build the new index before dropping the old one; a lot of CPU and I/O resources are required, which can overload the system if there isn’t a designated maintenance window; and backups become an issue.

If you’re using the FULL recovery model, the entire index rebuild operation is fully logged, which means the transaction log file must be at least as large as the index being rebuilt. It also means the next transaction log backup will essentially contain the entire index. This will have knock-on effects for log shipping because the log backup will have to be copied to the log shipping secondary servers and restored—essentially replaying the index rebuild; for transactional replication because the transaction log must be scanned by the replication log reader agent job, although it won’t be replicated; and for database mirroring because the transaction log generated by the index rebuild must be transmitted to the mirror server.

In the BULK_LOGGED recovery model, although the amount of transaction log generated is a lot less, the next transaction log backup will still contain roughly the same amount of data as if the operation were performed in the FULL recovery model because it must also back up the data extents changed by the minimally logged operation.
What’s worse is that an index rebuild operation will always build a new index, even if there’s minimal or no fragmentation. So yes, I’d say that it definitely isn’t optimal to rebuild all indexes every night.

Many people move to a strategy in which fragmentation is analyzed every night and only the fragmented indexes are processed. This is a step in the right direction, but running the sys.dm_db_index_physical_stats function is also very I/O intensive. (For an in-depth explanation of this function, see my blog post " Inside sys.dm_db_index_physical_stats".)

I prefer to analyze fragmentation over a week or two and then have index maintenance driven by a table containing the objects and indexes that are worth analyzing to see if fragmentation needs to be taken care of. This method cuts down on the resources required to do the analysis phase and can drastically reduce the length of time the index maintenance takes to run.

Once the analysis phase is working, I would then carefully move away from just using ALTER INDEX REBUILD and start including ALTER INDEX REORGANIZE operations. An index reorganize addresses only the fragmentation that exists, so it can be much more efficient for a lightly fragmented index than simply rebuilding the whole thing. (In fact, this is one of the reasons I wrote its predecessor—DBCC INDEXDEFRAG—for SQL Server 2000.)

Most people base the decision of which method to use on the value of the avg_fragmentation_in_percent column from the sys.dm_db_index_physical_stats function, using the numbers I came up with for SQL Server Books Online as the thresholds. (See my blog post “ Where do the Books Online index fragmentation thresholds come from?” for more information.)

This is just a quick overview of how to go about performing index maintenance—you also need to consider statistics maintenance. An index rebuild always rebuilds all the index column statistics with the equivalent of a full scan, whereas an index reorganize doesn’t touch statistics. Make sure you’re taking the appropriate action based on your decision to reorganize, rebuild, or do nothing. I would definitely encourage you to move to a more targeted approach to index maintenance rather than the sledgehammer method.

Discuss this Blog Entry 1

Holger Schmeling (not verified)
on Jun 14, 2010
Thank you Paul. Nice info. I wasn't really aware, one could calculate the required log file size in advance.
If I could, I'd add two additional things to keep in mind:
1) An index REBUILD will also rebuild the statistics that is related to the index – which is good and awkward. Good, because this creates up- to-date statistics. Additionally, these statistics are created with fullscan, so this rebuild will also create high quality statistics. Nice and lovely for the optimizer. Awkward, because this will invalidate any cached execution plans that make use of these updated statistics which in turn will lead to necessary re-compilations of these plans. So, the radical approach of rebuilding all indexes will very likely lead to otherwise unnecessary re-compilations.
Any index REORGANIZE will NOT update any statistics on the other hand.
2) Deciding on the returned fragmentation from sys.dm_db_index_physical_stats alone which action to perform may not be sufficient in all cases. I’ve discovered this problem recently and this article encouraged me to blog about it. You may read the regarding blog post here: http://sqlserver-online.blogspot.com/2010/06/do-you-rebuild-your-indexes.html.



Please or Register to post comments.

What's SQL Server Questions Answered?

Practical tips and answers to many of your questions about SQL Server including database management and performance issues.

Contributors

Paul S. Randal

Paul Randal worked on Microsoft's SQL Server team for nine years in development and management roles, writing many of the DBCC commands. Randal was ultimately responsible for SQL Server 2008'...

Kimberly L. Tripp

Kimberly L. Tripp has been working with SQL Server since 1990, and she’s worked as a consultant, trainer, speaker, and writer specializing in core SQL Server performance tuning and availability...
Blog Archive

Sponsored Introduction Continue on to (or wait seconds) ×