Rebuilding indexes

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:

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:

See “Creating and maintaining indexes” for information about configuration parameters that increase the speed of creating indexes.