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 |
| « ‹ | November 2024 | › » | ||||
| Sun | Mon | Tue | Wed | Thu | Fri | Sat |
| 1 | 2 | |||||
| 3 | 4 | 5 | 6 | 7 | 8 | 9 |
| 10 | 11 | 12 | 13 | 14 | 15 | 16 |
| 17 | 18 | 19 | 20 | 21 | 22 | 23 |
| 24 | 25 | 26 | 27 | 28 | 29 | 30 |
