Database Index Fragmentation: Diagnosing and Rebuilding SQL Server B-Trees

The Cost of Index Fragmentation

As databases process insertions and modifications, SQL Server B-Trees fragment:

• ◆Logical Fragmentation: Pages are no longer physically sequential on disk.
• ◆Page Splits: When rows are inserted into a full index page, the engine splits the page, leaving empty space and causing slow scans.

Monitoring index fragmentation is essential to maintain query performance.

DB Rule: Reorganize indexes with fragmentation between 5% and 30%. Rebuild indexes with fragmentation exceeding 30%.

Checking Index Fragmentation

-- DMV query to check index fragmentation levels
SELECT 
    a.index_id, name AS IndexName, avg_fragmentation_in_percent
FROM 
    sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS a
    JOIN sys.indexes AS b ON a.object_id = b.object_id AND a.index_id = b.index_id
WHERE 
    avg_fragmentation_in_percent > 10;

Rebuilding Indexes Online

For high-availability SaaS databases, rebuilding indexes blocks table writes. SQL Server allows online rebuilds:

-- Rebuilding index online without table locking
ALTER INDEX IX_Users_JoinedDate ON Users
REBUILD WITH (ONLINE = ON);

By automating index rebuilds, database administrators maintain query speeds under high transaction loads.