SQL Server 2008/2005 – Comparing Index rebuild/Reorganize and increase in Transaction Log file
Comparing ALTER INDEX options Index Rebuild and Reorganize:
1.Index Rebuild requires building the new index before dropping the old one. This means there has to be enough free space in the database to accommodate the new index; otherwise the database will grow to provide the required free space. This can be problematic for large indexes. Index Reorganize only requires 8KB of additional space in the database.
2.Index Rebuild can use multiple CPUs so the operation runs faster. Index Reorganize is always single-threaded.
3.Index Rebuild may require long-term locks on the table that can limit concurrent operations. Index REORGANIZE doesn’t hold blocking locks and it’s always been an online operation.
4.Index Rebuild can use minimal-logging to … Read the rest