Index structure and Types -- Part 4 : Rebuild vs Reorganize

Reorganizing an Index

To reorganize one or more indexes, use the ALTER INDEX statement with the REORGANIZE clause. This statement replaces the DBCC INDEXDEFRAG statement. To reorganize a single partition of a partitioned index, use the PARTITION clause of ALTER INDEX.
Reorganizing an index defragments the leaf level of clustered and nonclustered indexes on tables and views by physically reordering the leaf-level pages to match the logical order (left to right) of the leaf nodes. Having the pages in order improves index-scanning performance. The index is reorganized within the existing pages allocated to it; no new pages are allocated. If an index spans more than one file, the files are reorganized one at a time. Pages do not migrate between files.
Reorganizing also compacts the index pages. Any empty pages created by this compaction are removed providing additional available disk space. Compaction is based on the fill factor value in the sys.indexes catalog view.
The reorganize process uses minimal system resources. Also, reorganizing is automatically performed online. The process does not hold long-term blocking locks; therefore, it will not block running queries or updates.
Reorganize an index when the index is not heavily fragmented. See the previous table for fragmentation guidelines. However, if the index is heavily fragmented, you will achieve better results by rebuilding the index.

Large Object Data Type Compaction

Besides reorganizing one or more indexes, large object data types (LOBs) that are contained in the clustered index or underlying table are compacted by default when an index is reorganized. The data types image, text, ntext, varchar(max), nvarchar(max), varbinary(max), and xml are large object data types. Compacting this data can cause better disk space use.

·         Reorganizing a specified clustered index will compact all LOB columns that are contained in the leaf level (data rows) of the clustered index.

·         Reorganizing a nonclustered index will compact all LOB columns that are nonkey (included) columns in the index.

·         When ALL is specified, all indexes associated with the specified table or view are reorganized and all LOB columns associated with the clustered index, underlying table, or nonclustered index with included columns are compacted.

·         The LOB_COMPACTION clause is ignored if LOB columns are not present.

Rebuilding an Index

Rebuilding an index drops the index and creates a new one. In doing this, fragmentation is removed, disk space is reclaimed by compacting the pages using the specified or existing fill factor setting, and the index rows are reordered in contiguous pages (allocating new pages as needed). This can improve disk performance by reducing the number of page reads required to obtain the requested data.
The following methods can be used to rebuild clustered and nonclustered indexes:

·         ALTER INDEX with the REBUILD clause. This replaces DBCC DBREINDEX.

·         CREATE INDEX with the DROP_EXISTING clause.

Each method performs the same function, but there are advantages and disadvantages to consider as shown in the following table.

Functionality	ALTER INDEX REBUILD	CREATE INDEX WITH DROP_EXISTING
Index definition can be changed by adding or removing key columns, changing column order, or changing the column sort order.*	No	Yes**
Index options can be set or modified.	Yes	Yes
More than one index can be rebuilt in a single transaction.	Yes	No
Most index types can be rebuilt online without blocking running queries or updates.	Yes	Yes
Partitioned index can be repartitioned.	No	Yes
Index can be moved to another filegroup.	No	Yes
Additional temporary disk space is required.	Yes	Yes
Rebuilding a clustered index rebuilds associated nonclustered indexes.	No Unless the keyword ALL is specified.	No Unless the index definition changed.
Indexes enforcing PRIMARY KEY and UNIQUE constraints can be rebuilt without dropping and re-creating the constraints.	Yes	Yes
Single index partition can be rebuilt.	Yes	No

*A nonclustered index can be converted to a clustered index type by specifying CLUSTERED in the index definition. This operation must be performed with the ONLINE option set to OFF. Conversion from clustered to nonclustered is not supported regardless of the ONLINE setting.
**If the index is re-created by using the same name, columns and sort order, the sort operation may be omitted. The rebuild operation checks that the rows are sorted while building the index.
You can also rebuild an index by first dropping the index with the DROP INDEX statement and re-creating it with a separate CREATE INDEX statement. Performing these operations as separate statements has several disadvantages, and we do not recommend this.

Disabling Nonclustered Indexes to Reduce Disk Space During Rebuild Operations

When a nonclustered index is disabled, the index data rows are deleted, but the index definition remains in metadata. The index is enabled when it is rebuilt. When the nonclustered index is not disabled, the rebuild operation requires enough temporary disk space to store both the old and new index. However, by disabling and rebuilding a nonclustered index in separate transactions, the disk space made available by disabling the index can be reused by the subsequent rebuild or any other operation. No additional space is required except for temporary disk space for sorting. This is typically 20 percent of the index size. If the nonclustered index is on the primary key, any active, referencing FOREIGN KEY constraints will automatically be disabled. These constraints must be manually enabled after the index is rebuilt.

Rebuilding Large Indexes

Indexes with more than 128 extents are rebuilt in two separate phases: logical and physical. In the logical phase, the existing allocation units used by the index are marked for deallocation, the data rows are copied and sorted and then moved to new allocation units created to the store the rebuilt index. In the physical phase, the allocation units previously marked for deallocation are physically dropped in short transactions that happen in the background, and do not require many locks.

Setting Index Options

Index options cannot be specified when reorganizing an index. However, the following index options can be set when you rebuild an index by using either ALTER INDEX REBUILD or CREATE INDEX WITH DROP_EXISTING:

PAD_INDEX	DROP_EXISTING (CREATE INDEX only)
FILLFACTOR	ONLINE
SORT_IN_TEMPDB	ALLOW_ROW_LOCKS
IGNORE_DUP_KEY	ALLOW_PAGE_LOCKS
STATISTICS_NORECOMPUTE	MAXDOP

Additionally, the SET clause in the ALTER INDEX statement lets you set the following index options without rebuilding the index:

ALLOW_PAGE_LOCKS	IGNORE_DUP_KEY
ALLOW_ROW_LOCKS	STATISTICS_NORECOMPUTE