Rebuilding indexes reclaims space in the binary trees (a tree where all leaf pages are the same distance from the root page of the index). As pages are split and rows are deleted, indexes may contain many pages that contain only a few rows. Also, if the 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 recreating the index.
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 recreate a clustered index or run reorg rebuild on a data-only-locked or all-pages-locked table, all nonclustered indexes are recreated, since creating the clustered index moves rows to different pages.
When system activity is low:
Delete all indexes to allow more efficient bulk inserts.
Create a new group of indexes to help generate a set of reports.
