Rebuild or Reorganize: SQL Server Index Maintenance

Index optimization is probably one of the most critical task every database support personnel has to perform on a regular basis. Based on DML operations in a particular environment, we adopt various optimization tasks, steps and strategies that suit our needs. Some tables or indexes may need frequent optimization, some do not need it at all for a longer period of time.

To optimize an index we have two options, REBUILD and REORGANIZE. Both work differently and have different effects. There are some differences which we should be aware of for better understanding of how each T-SQL command works and what does it do for us.

Index Rebuild : This process drops the existing Index and Recreates the index.
USE AdventureWorks;
GO
ALTER INDEX ALL ON Production.Product REBUILD
GO
Index Reorganize : This process physically reorganizes the leaf nodes of the index.
USE AdventureWorks;
GO
ALTER INDEX ALL ON Production.Product REORGANIZE
GO
Recommendation: Index should be rebuild when index fragmentation is great than 40%. Index should be reorganized when index fragmentation is between 10% to 40%. Index rebuilding process uses more CPU and it locks the database resources. SQL Server development version and Enterprise version has option ONLINE, which can be turned on when Index is rebuilt. ONLINE option will keep index available during the rebuilding.

Good to Know some key points:

1.      When a non-clustered index is rebuilt, only the associate statistics for this index will be rebuilt.

2.      Rebuilding a clustered index does not rebuild associated non-clustered indexes unless the keyword ALL is specified.

3.      “HEAP” cannot be optimized. If “ALL” is specified and the underlying table is a heap, the rebuild operation has no effect on the table. Any non-clustered indexes associated with the table are rebuilt.

4.      The rebuild operation can be minimally logged if the database recovery model is set to either bulk-logged or simple.

5.      The options ONLINE = ON and IGNORE_DUP_KEY = ON are not valid while rebuilding an XML index or a spatial index.

6.      “STATISTICS_NORECOMPUTE = ON” means Out-of-date statistics are not automatically recomputed. “STATISTICS_NORECOMPUTE = OFF” means automatic statistic updating is enabled.

7.      If index options are not specified, the existing index option values stored in sys.indexes will be used.

8.      ALTER INDEX cannot be used to repartition an index or move it to a different filegroup. This statement cannot be used to modify the index definition, such as adding or deleting columns or changing the column order.

9.      The values for ONLINE, MAXDOP, and SORT_IN_TEMPDB are not stored in the system catalog. You need to specify this OPTION in the index rebuild statement.

10. Reorganizing an index is always performed online. The process does not hold locks long term hence it does not block queries or updates that are running.

11. When you execute ALTER INDEX ALL … on a table, only the statistics associated with indexes are updated. Automatic or manual statistics created on the table will not be updated.

12. Index REBUILD can be a Parallel operation. Index REORGANIZE is always serial operation.

13. Rebuilding or reorganizing small indexes (which has 128 or less extents) often does not reduce fragmentation.

14. Reorganizing an index uses minimal system resources and also compacts the index pages.

15. Reorganizing an index does not update associate statistics.

Index Optimization Guideline:

The guideline that Microsoft has provided in the MSDN is a general guideline regardless of any DML operations happening in the database which need to be further reviewed by the database administrator based on his/her workload scenario to establish a better threshold.

The sys.dm_db_index_physical_stats can be used to determine fragmentation levels in a specific index, in all indexes on a table or indexed view, in all indexes in a database, or in all indexes in all databases. The following table explains three important columns of the system function which need to be researched closely:
Action threshold recommended by Microsoft.

Column	Description
avg_fragmentation_in_percent	The percent of logical fragmentation (out-of-order pages in the index).
fragment_count	The number of fragments (physically consecutive leaf pages) in the index.
avg_fragment_size_in_pages	Average number of pages in one fragment in an index.

avg_fragmentation_in_percent	T-SQL Command
> 5% and < = 30%	ALTER INDEX REORGANIZE
> 30%	ALTER INDEX REBUILD (All Edition) ALTER INDEX REBUILD WITH (ONLINE = ON) (Enterprise Edition)
Number of Extents > 128	Will be a good candidate for index optimization

The above threshold is a recommendation only. As every environment is different therefore it is a good idea to research the best threshold that will suit your need.