Article ID: 2407439 - View products that this article applies to.
Consider the following scenario:
In this scenario, you will notice that, in the newer version, the transaction log for the database expands at a much higher rate than in the older version 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.
Online Index Rebuild is a fully logged operation on SQL Server 2008 and later versions, whereas it is minimally-logged in SQL Server 2005. The change was made to ensure data integrity and more robust restore capabilities. .
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.
The preferred method depends on your business requirements.
You may want to evaluate your current storage capacity to ensure that it can accommodate the increased size of transaction log in the SQL Server versions later than SQL Server 2005. In addition, Microsoft recommends that administrators test applications that use Log Shipping, Database Mirroring and Transactional Replication in order to establish new baselines for how fast those utilities will perform on SQL Server 2008 or later versions.
If log size needs to be controlled due to storage capacity limitations, you may consider using the BULK LOGGED recovery model for the duration of the online index rebuild. Please refer to the following MSDN articles to understand the pros and cons of using the BULK LOGGED model:
Note that under Bulk Logged recovery the transaction log size may be smaller when minimally-logged operations are performed, but the actual transaction log backup size may still be large. This is because the log backup captures all the database pages changed by the minimally-logged operations.
(http://go.microsoft.com/fwlink/?LinkId=151500)for other considerations.
Article ID: 2407439 - Last Review: May 24, 2011 - Revision: 5.0
Contact us for more help