A DBA reviews a clustered index that is 45 percent fragmented, with many partially filled pages after heavy updates. The database is in production and the index must remain available during maintenance. What is the most appropriate action?
Show answer and explanation
Answer: C
For heavy fragmentation with sparse pages, rebuilding the index is the most effective remedy because it recreates the index structure from scratch. Azure SQL Database supports online index rebuilds, which keep the index available for queries during the operation, satisfying the availability requirement. Reorganize is lighter and can be paused, but it is less effective against severe fragmentation. Updating statistics does not change the physical layout, and shrinking files tends to fragment the database further.
Why the other options are wrong
- A. Reorganize is an online, lower-impact option suited to light to moderate fragmentation. At 45 percent with sparse pages, it would take longer and leave the index less compact than a rebuild would.
- B. Updating statistics refreshes the data distribution the optimizer uses. It does not change the physical page layout or the fragmentation level, so it does not address this symptom.
- D. Shrinking data files moves pages around and typically increases fragmentation. It also does not rebuild the index, so the problem would likely get worse.