Fragmented Indexes

Fragmented indexes occur when the logical order of index pages does not match their physical order due to frequent data modifications (INSERT, UPDATE, DELETE). Historically, database administrators have followed certain thresholds to rebuild or reorganize indexes to improve performance. However, with the advent of SSDs (Solid-State Drives), some of these best practices are outdated.

Why Fragmented Indexes Are a Problem

When an index is fragmented, the data becomes less efficient to retrieve, leading to wasted disk space and higher I/O costs. Traditionally, fragmentation would also lead to performance issues with spinning disks, which required defragmentation to improve data access speeds. With SSDs, the impact on data retrieval is minimal, but fragmented indexes still cause unnecessary overhead in other ways:

  • Wasted Disk Space: Fragmented indexes cause more pages to be used than necessary, wasting valuable storage space.

  • Memory Overhead: In SQL Server, entire pages are stored in memory, not just the data. By recovering space within fragmented pages, you reduce memory consumption as fewer pages are needed to store the same data.

  • Defragmenting SSDs: SSDs handle fragmented pages much better than spinning disks, so the traditional thresholds for rebuilding or reorganizing indexes are less relevant. The focus today should be on space recovery rather than performance improvements.

Updated Best Practices for Fragmented Indexes

In the past, the recommendation was to reorganize indexes when fragmentation was between 5-30% and rebuild indexes when fragmentation exceeded 30%. These thresholds were primarily designed for environments using spinning disks, where reordering the physical structure significantly improved performance.

With modern SSDs, however, the main benefit of defragmenting indexes is recovering space, not reordering the data on disk. This recovered space reduces both disk usage and memory overhead, since fewer pages are needed to store data efficiently.

  • Reorganize or Rebuild?: Focus less on fragmentation thresholds and more on space recovery. You may still want to reorganize or rebuild fragmented indexes in cases where disk space or memory usage becomes an issue, even if performance isn’t noticeably impacted.

How to Reorganize or Rebuild Fragmented Indexes

Reorganizing an Index

To reorganize an index (for minor fragmentation):

ALTER INDEX [IX_IndexName] ON [TableName] REORGANIZE;
GO

Rebuilding an Index

To rebuild an index (for more severe fragmentation or to recover space):

ALTER INDEX [IX_IndexName] ON [TableName] REBUILD;
GO

Conclusion

While index fragmentation still matters, the focus has shifted from performance improvement to space recovery, especially in systems using SSDs. By reducing the number of fragmented pages, you not only save on disk space but also reduce the memory footprint, since fewer pages are loaded into memory.

How Aireforge Handles Fragmented Indexes

Aireforge automatically detects fragmented indexes and provides recommendations on how to address them. This includes offering configurable thresholds for both fragmentation percentage and the minimum number of pages to check.

Here’s how Aireforge handles fragmented indexes:

  1. Fragmentation Thresholds: Aireforge uses customizable thresholds for fragmentation percentage and page count .
    • Default Fragmentation Threshold: Aireforge checks indexes with more than 5% fragmentation, but this can be adjusted.
    • Minimum Page Count: By default, Aireforge ignores indexes with fewer than a specified number of pages (e.g., 100), since fragmentation in smaller indexes has minimal impact.
  2. Action Based on Fragmentation:
    • Rebuild for High Fragmentation: If fragmentation exceeds 30% and the SQL Server edition supports online index rebuilds (Enterprise or Developer editions), Aireforge suggests rebuilding the index to reduce fragmentation and recover space.
    • Reorganize for Lower Fragmentation: If fragmentation is between 5% and 30%, Aireforge recommends reorganizing the index to improve efficiency.
  3. Space Recovery Focus: For SSD-based systems, Aireforge focuses on recovering space and reducing the number of pages, which helps to reduce disk usage and memory overhead.
  4. No Action Necessary: If no fragmented indexes meet the defined thresholds, Aireforge passes the check, indicating that no further action is required.