Index fragmentation

In one line: “fragmentation” lumps two different things together. Pages out of order (avg_fragmentation_in_percent) cost almost nothing once they’re in memory. Pages half empty (avg_page_space_used_in_percent) cost reads and buffer pool memory on every query. Watch density, not the fragmentation percentage.

Run it yourself: the scripts that reproduce everything on this page are in sqlserver-demos/index-fragmentation on GitHub.

What you’ll see

  • A maintenance job rebuilding indexes every night because they’re “over 30% fragmented”, with nothing faster afterwards.
  • An index with near-zero fragmentation that still reads twice the pages it should. In the demo, deleting every second row left 0.12% fragmentation and 2.00× the reads and buffer pool.
  • Large log backups and busy availability group replicas on maintenance nights.

Why it happens

A logical read is a page touched in memory. Once the page is cached, where it sits in the file doesn’t matter. In the demo, an index at 99.97% fragmentation did exactly the same reads as the clean one, to the page.

Density is different. Half-empty pages mean twice the pages for the same rows, cached half-empty too. A random-GUID clustering key showed both conditions, and its extra reads matched its density ratio (1.44×) to two decimal places. The 99% fragmentation added nothing.

Page order matters only on a cold read, and only when pages are scattered rather than just reversed. The GUID index needed 6.7× as many read I/Os.

What to do

  • Base maintenance on avg_page_space_used_in_percent and how the index is used, not on the 5% / 30% rule. That rule would have rebuilt an index for no gain and skipped the one that saved 50% of its reads.
  • Know what each operation does:
    • REBUILD refreshes statistics with a full scan.
    • REORGANIZE doesn’t touch statistics, and wrote about 300× the log (SIMPLE recovery).
    • If you rebuild “for the statistics”, run UPDATE STATISTICS ... WITH FULLSCAN instead.
  • Use FILLFACTOR below 100 only for keys whose inserts land all over the range. In the demo, FILLFACTOR 80 took 67 page splits where 100 took 2,803. It’s pure cost for an ever-increasing key.

What doesn’t help

  • Rebuilding on the fragmentation percentage alone. On a warm cache, page order costs nothing.
  • Shrinking data files. The demo uses a shrink precisely because it’s the most reliable way to fragment every index in the file.

Check your server

-- Index density in the current database. Low avg_page_space_used_in_percent is the costly kind.
-- SAMPLED mode reads part of each index; run it off-peak on large databases.
SELECT OBJECT_SCHEMA_NAME(ips.object_id) AS schema_name,
       OBJECT_NAME(ips.object_id)        AS table_name,
       i.name                            AS index_name,
       ips.page_count,
       CAST(ips.avg_page_space_used_in_percent AS decimal(5, 1)) AS density_pct,
       CAST(ips.avg_fragmentation_in_percent   AS decimal(5, 1)) AS fragmentation_pct
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'SAMPLED') AS ips
JOIN sys.indexes AS i ON i.object_id = ips.object_id AND i.index_id = ips.index_id
WHERE ips.page_count >= 1000
  AND ips.index_level = 0
ORDER BY ips.avg_page_space_used_in_percent;

See it happen

The index-fragmentation demo builds four tables with the same 100,000 rows and different page layouts. It measures warm and cold reads for each, runs REBUILD and REORGANIZE against the 5% / 30% rule, and prices FILLFACTOR.

Related: heaps and forwarded records · data compression · missing-index suggestions

Measured on SQL Server 2022 CU25 (16.0.4255.1), 8 schedulers, NVMe storage. Your numbers will differ; the behaviour shouldn’t.

Part of SQL Server Demos: short explanations of SQL Server problems, each backed by a runnable demo in MarlonRibunal/sqlserver-demos on GitHub. Written with the help of AI; every number comes from running the demo on SQL Server. How this was made.

Verified by MonsterInsights