TLog impact when Rebuilding SQLServer Indexes

Let’s talk about Rebuilding SQLServer Indexes and TLog

First some footnotes:

-       Rebuilding indexes online is only available in Enterprise (or Developer) edition, so reorganizing is the only online option on Standard Edition.

-       Reorganizing indexes is always online and it doesn’t require a schema mod lock, so it can provide better concurrency.

-       Reorganizing only defragments the leaf level of the index. On large tables it will take longer than a rebuild would take. But it’s nice that you can reorganize for a while and then stop without facing a massive rollback.

-       For partitioned indexes built on a partition scheme, you can use either of these methods (Rebuild/reorganize) on a complete index or a single partition of an index. Unfortunately, partition level rebuilds are offline until SQL Server 2014.

-       A rebuild of an index needs to be done when there is no (/less) activity to avoid locking issues.

 

The facts

When I was at one of our customers a few weeks ago, they had some questions about the rebuild index process. On some servers it caused big TLog growths, and on other servers, it didn’t. So I did a quick investigation on the differences in versions and options. These are my findings:

-       Rebuilding an index needs enough space to create the new index. A simplified rule of thumb seems to be that you need about 120% of the space used by the original index. This may be in the database or in tempdb, depending on whether SORT_IN_TEMPDB is ON or OFF. If possible, have SORT_IN_TEMPDB = ON this will reduce some of the logging that is done.

-       If you rebuild all of the indexes between LOG backups, then all of the logs for reindexing all of the indexes will be in the log file. Therefore, major reorganization needs to have the appropriate resources of disk space, log space, and so on. A solution can be to reorganizing one table at a time and doing a log backup after each.

-       You could change to a minimally logged recovery model such as SIMPLE or BULK_LOGGED during the Index Rebuild, but be aware of the impact of that process on your production databases. Make sure that your important database is back in FULL recovery after the rebuild process, no matter what happened.

-       A log file can be shrunk, but only after the high order pages are freed by a log backup. This is usually a cycle of backup log, DBCC SHRINKFILE and then check the space and try again.

-       In SQLServer 2008 (or later versions), the transaction log for the database expands at a much higher rate than in the older version (SQLServer 2005 or before) for the same operation.
As as a result of the increased transaction log size, utilities such as log shipping, database mirroring, transaction log backups, and transactional replication may run slower than they used to in SQL Server 2005.

 

Possible resolutions:

-       Consider reducing the frequency of online index rebuild operations. To ensure optimal index usage and performance, update statistics more frequently rather than reorganizing the indexes. Also, if query plan stability is an issue, consider using plan guides or other techniques for tuning performance. Please refer to the following links for further information.

-       You can also consider rebuilding the indexes offline.To rebuild indexes offline, the ALTER INDEX statements have the ONLINE option set to OFF.

 

More information about the TLog space for Index operations can be found at:

http://msdn.microsoft.com/en-us/library/ms184246.aspx