Maintaining indexes on your table is an important part of keeping your database healthy and its performance adequate. There are two maintenance operations for any index: rebuilding and reorganizing. Both operations were designed to get rid of index fragmentation, but differ in how this is done.
| Rebuild | Reorganize | |
|---|---|---|
| What it does | Drops the existing index and recreates it from scratch. | Physically reorganizes the leaf nodes of the index. |
| When to use | Index fragmentation >= 40% | Index fragmentation >= 10% and < 40% |
| Impact on system | High. Database will be offline during process, unless you have Enterprise Edition and have enabled the ONLINE option. Online rebuild requires more resources than offline rebuild. | High. Reorganize runs on online database. |
| Common options | Process can be run on all indexes of all tables on a database, or can be narrowed down to specific databases, specific tables, or even a single index. | |
| Function-specific options | Enteprise Edition has an option available to perform an online rebuild of the index. This keeps the index available during rebuild, allowing database access. | N/A |
