Today, let us discuss on Index Maintenance on SQL Server. Indexes tend to get fragmented as the data gets updated. If the update is under the free space on page which was specified using Fill Factor of index creation or Rebuild then it would not be fragmented. The clustered index is the index by which the Leaf Nodes are Stored and thus does not require additional space.
ALTER INDEX ... REORGANIZE
ALTER INDEX .... REBUILD
Here are some Pros and Cons between the two:
1) Index rebuild works by re-creating the index internally and when that completes, it drops the existing index where as index reorganize is the process of physically re-organizing the leaf nodes of the index.
2) During the index rebuild process, the statistics are also re-computed – same as when a new index gets created. Reorganize on the other hand does not update the statistics.
3) Reorganize essentially just swaps one page with another and thus does not require free space for this operation like rebuild does.
4) If a sort operation is not required, or if the sort can be performed in memory, the SORT_IN_TEMPDB option is ignored. The default value is OFF.
5) A reorganize can remove almost all of the logical fragmentation but it cannot necessarily fix extent fragmentation in which the previous and the next extents are physically contiguous.
6) Another point to note is that an index (clustered or non-clustered) cannot be built online if it contains LOB data (text, ntext, varchar(max), nvarchar(max), varbinary(max), image and xml data type columns).
7) The ALTER INDEX…REORGANIZE command shown above is the same as DBCC INDEXDEFRAG but there is one difference. ALTER INDEX…REORGANIZE has some additional features like large objects compaction (LOB_COMPACTION). And this is an online operation.
Let us quickly check this in a Demo(Please use a Big Table with a Index on it)
Begin Tran
Alter index [Index_name] on [Table_name] reorganize
From another Window do a select query from above table and it will execute successfully.
Now lets do Rebuild
Begin Tran
Alter index [Index_name] on [Table_name] rebuild
From another Window do a select query from above table and it will just need to wait for above transaction is finished.
Now, lets do the same for Rebuild with Online= On
Begin Tran
Alter index [Index_name] on [Table_name] rebuild with (Online=On)
Now, it will give a message saying online rebuild will happen but due to the begin Tran table wont be able to concurrently accessed.
8)If we have partitions of an index, then you cannot rebuild a single partition online. You can reorganize a single index partition online. If you want to rebuild an index with multiple partitions in an online environment, you need to rebuild the entire index which means rebuilding all the partitions.
Note: The auto create statistics database option when On, we can see some Statistics created automatically on Database with some name like _SM_sys_xxxx.
These statistics are not updated when the Index Rebuild occurs which needs to be updated manually. Please read my Demo on this here.