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 reduce transaction log growth. Index Reorganize is always fully logged, but doesn’t prevent transaction log clearing.
5.Index Rebuild will automatically rebuild all index-column statistics, whereas Reorganize won’t update statistics at all.
6.An index rebuild will always rebuild the entire index, regardless of the extent of fragmentation. This means an index rebuild for a lightly fragmented index is really overkill.
7.An index reorganize will only take care of the existing fragmentation. This makes it a better choice for removing fragmentation from a lightly fragmented index, but a poor choice for removing fragmentation from a heavily fragmented index.
Now determining the thresholds for choosing between rebuild and reorganize is very important and mostly everyone uses avg_fragmentation_in_percent column in the output from sys.dm_db_index_physical_stats.
Recommendations as below:
->0 percent to 10 percent: do nothing
->10 percent to 30 percent: use ALTER INDEX … REORGANIZE
->30 percent and higher: use ALTER INDEX … REBUILD
Note: If Database mirroring is used then it requires database to be in full recovery model. This means index rebuild operations will be fully logged. In many Environments, DBA’s find this produces too much transaction log to efficiently send between the mirroring principal and mirror. In such cases, it can be better to reorganize indexes for lower fragmented tables to minimize the transaction log.
Scripts:
In many environments, a standard script is used for doing Index Rebuild/Reorganize based on percent of fragmentation. This can often be based on someone else’s index-maintenance scripts to save time like Ola Hallengren which has some comprehensive and widely used scripts after testing them in different scenarios in test environment and understanding how it works. You can always write your own script to perform the same.
It is always recommended to leave the TLOG at the size it needs to accommodate the growth needed for it to perform. If you cannot accommodate this then we can opt for one of the solution below:
•Don’t rebuild all your indexes within one transaction like maintenance plans. Instead split them in to smaller batches with log backups between.
•We can use selective index rebuilds instead rebuilding all of them as it’s really pointless rebuilding tables which are not fragmented. We can use the above batch approach to do this and issue backup commands in between.
•Another method is to change the Recovery Model to SIMPLE but this won’t actually solve your problem if the index rebuilds are in a transaction as you still need checkpoints to clear down the log in between rebuilds. But if you take above batch approach then you clear the log file usage by issues frequent commits which marks log file space for reuse. We should always remember that if you change recovery model then you need either a full or diff backup after you switch the database back to full recovery otherwise you’ll invalidate TLOG backups.
•To summarize, any of the above approach doing the rebuild in smaller batches or changing recovery model or issuing log backups in between the rebuilds. But I always recommend to leave the T log at its max size.
•Another thing is if log file is not properly pre-sized for your environment then it will cause index rebuild or reorganize to take a lot of time both doing log growths and then Zero initialization for it whenever needed. In fact, have a proper sized log file will cut down the rebuild/reorganize time.
•You should rebuild your indexes at least once in a while(Weekly) and the frequency entirely depends on the fillfactor and the amount of data that is changed between your rebuilds which causes index fragementation.
Note: I also always advise against using shrinking database. It is for two main reasons
1. It will make indexes fragmented.
2. When the file grows again back eventually it will cause or produce NTFS fragmentation.
Internals:
Index rebuild in older days like SQL Server 2000 and before always needed to have blocking locks for the duration of the rebuild operation. Rebuilding a clustered index meant an exclusive table lock (no concurrent readers or writers). Rebuilding non-clustered indexes meant a shared table lock (no concurrent writers to the table).
With the advent of online index operations in the Enterprise Edition of SQL Server 2005, locking was changed for online operations. Blocking locks were only held for short periods at the start and end of the operation.
In SQL Server 2005 and above, all index rebuild operations use minimal logging if you are using bulk-logged or simple recovery models. All operations are fully logged in the full recovery model.
From SQL Server 2008 onward, online index operations were changed to be fully logged in all recovery models.
If you are concerned on transaction log growth and concurrent user access the data then it is more important we need to revert to offline index rebuilds to get that minimal-logging behavior back. Another thing to consider is using ALTER INDEX … REORGANIZE to remove fragmentation. This always operates without causing blocking and can reduce the amount of transaction log.