SQL Server  

SQL Server Index Fragmentation: What It Is and How to Fix

Many developers and DBAs encounter the term index fragmentation, and some have struggled with its consequences while building and maintaining databases throughout an application’s lifecycle.

In this article, we’ll go step-by-step to understand:

  • What index fragmentation?
  • When and why it happens
  • The different types (internal vs external)
  • How to avoid it, and fix it

1. Quick Recap: What is an Index?

An index is a data structure that helps locate specific rows quickly. It works by storing sorted values from one or more columns of a table, along with pointers to the corresponding rows.

In SQL Server, data is stored in pages, fixed-size blocks (typically 8KB each). When the database needs to read data, it loads an entire page into memory.

This is more efficient than fetching single rows, as it reduces disk I/O.

When a query is executed:

  • SQL Server checks if an index exists for the queried columns.
  • If it exists, SQL Server uses it to locate the relevant pages.
  • Only those specific pages are read, instead of scanning the whole table.

Query executed

2. What is Index Fragmentation?

Index fragmentation occurs when the logical order of data in an index does not match the physical storage order.

  • Logical Order: An index is designed to provide a quick lookup for data based on specific key values. Ideally, the data within the index pages should be stored in a contiguous, ordered manner based on these key values.
  • Physical Order: How pages are actually stored on disk.

When these orders are out of sync, SQL Server has to jump around more, increasing I/O and slowing queries.

Example scenario

  1. You insert new rows into a table with a clustered index.
  2. SQL Server tries to keep them in order by key.
  3. If the target page is full, SQL Server splits the page into two, moving some rows to the new page.
  4. Now the physical page order may no longer match the logical order.

Inseration

Internal vs External Fragmentation

There are two main types of fragmentation:

Internal Fragmentation

  • Occurs inside pages.
  • Pages are only partially filled, leaving empty space.
  • Often happens after deletes or updates that shrink data.
  • Leads to wasted space and more pages to scan.

External Fragmentation

  • Occurs when the order of pages no longer matches their logical sequence.
  • Caused by page splits that scatter data across the file.
  • Leads to more random I/O.
Logical Order: Page 100 → Page 101 → Page 102
Physical Order: Page 100 → Page 300 → Page 101 → Page 500 → Page 102

Why Does It Matter?

Fragmentation increases

  • I/O operations (more pages read)
  • CPU usage (more pointer lookups)
  • Query execution time

Especially in large datasets, even small increases in page reads can cause significant slowdowns.

4. How to Fix Fragmentation

  • REORGANIZE: Defragments the index by physically reordering leaf pages. Lightweight, online. 
    ALTER INDEX IndexName ON TableName REORGANIZE;
  • REBUILD: Drops and recreates the index. Removes all fragmentation. Can be offline or online (Enterprise Edition). 
    ALTER INDEX IndexName ON TableName REORGANIZE;

Important Note

Microsoft recommends:

  • <5% fragmentation → No action needed.
  • 5–30% fragmentation → REORGANIZE.
  • >30% fragmentation → REBUILD.

By following these guidelines, you can keep your SQL Server indexes healthy and your queries fast.

Membership not found