Rebuilding indexes reclaims space in the B-trees. As pages are split and rows are deleted, indexes may contain many pages that contain only a few rows. Also, if your application performs scans on covering nonclustered indexes and large I/O, rebuilding the nonclustered index maintains the effectiveness of large I/O by reducing fragmentation.
You can rebuild indexes by dropping and re-creating the index. If the table uses data-only locking, you can run the reorg rebuild command on the table or on an individual index.
Re-create or rebuild indexes when:
Data and usage patterns have changed significantly.
A period of heavy inserts is expected, or has just been completed.
The sort order has changed.
Queries that use large I/O require more disk reads than expected, or optdiag reports lower cluster ratios than usual.
Space usage exceeds estimates because heavy data modification has left many data and index pages partially full.
Space for expansion provided by the space management properties (fillfactor, expected row size, and reserve page gap) has been filled by inserts and updates, resulting in page splits, forwarded rows, and fragmentation.
dbcc has identified errors in the index.
If you re-create a clustered index or run reorg rebuild on a data-only-locked table, all nonclustered indexes are re-created, since creating the clustered index moves rows to different pages.
You must re-create nonclustered indexes to point to the correct pages.
In many database systems, there are well-defined peak periods and off-hours. You can use off-hours to your advantage for example to:
Delete all indexes to allow more efficient bulk inserts.
Create a new group of indexes to help generate a set of reports.
See “Creating and maintaining indexes” for information about configuration parameters that increase the speed of creating indexes.
