Fragmentation, rebuilds, reorgs: the myth of 30%

“Rebuild indexes with more than 30% fragmentation. Reorganize between 5% and 30%. Leave the rest alone.”

This advice is in a million tutorials, old Microsoft docs, and most shops’ maintenance plans. It comes from a single example Paul Randal (who literally wrote the SQL Server storage engine) gave on a forum post in 2008. He has since spent 17 years saying “I’m sorry, please stop quoting me on that, I made the numbers up for illustration.”

Today we’ll talk about what actually matters for index maintenance in 2026, on modern SSD storage, with RCSI enabled. It’s less than you’ve been told and simpler than the old maintenance plans.

What fragmentation actually is

Two kinds, confusingly named similarly:

• External (logical) fragmentation — the physical order of index leaf pages doesn’t match the logical order. A range scan hops around the disk.
• Internal fragmentation — leaf pages are not full. Half-empty pages waste RAM and disk.

sys.dm_db_index_physical_stats reports both:

SELECT
    OBJECT_NAME(ips.object_id)                     AS table_name,
    i.name                                          AS index_name,
    ips.index_type_desc,
    ips.avg_fragmentation_in_percent               AS logical_frag_pct,
    ips.avg_page_space_used_in_percent             AS page_fill_pct,
    ips.page_count,
    ips.record_count
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
ORDER BY ips.avg_fragmentation_in_percent DESC;

The 'SAMPLED' mode is fast. 'DETAILED' is thorough but can take hours on huge tables. Start with sampled.

Why the old advice was for spinning rust

The “rebuild above 30%” advice made sense when:

• Storage was spinning disks. Random I/O was 10× slower than sequential.
• Range scans were the dominant access pattern.
• Servers had 32GB of RAM and couldn’t keep much data cached.
• SQL Server couldn’t do batch-mode or modern parallel scans.

In 2026:

• Most production SQL Servers run on SSDs or NVMe. Random I/O is almost as fast as sequential.
• Memory is cheap. Most hot data lives in RAM (the buffer pool). Fragmentation of pages on disk is moot when the page is in cache.
• Workloads are diverse — seeks on narrow indexes dominate for OLTP, and the covering-index pattern means fewer large range scans.
• Columnstore and batch mode have their own storage patterns entirely.

The net: fragmentation matters less than it did in 2008. It’s not free — rebuilding defragmented pages still helps large scans and fills up better — but it’s no longer the number-one priority.

What to actually do

Pragmatic advice for a 2026 Runehold-sized workload:

1. Don’t run maintenance every night. Weekly is usually fine; some shops do monthly.
2. Use Ola Hallengren’s scripts, not the built-in maintenance plan. We’ll cover them in lesson 35.
3. Skip anything under ~1,000 pages. Maintenance on tiny indexes is pure overhead.
4. Prefer REORGANIZE by default for moderate fragmentation. Online, low-impact, doesn’t take locks that block queries.
5. Use REBUILD WITH (ONLINE = ON) (Enterprise only) for heavy fragmentation on hot tables.
6. On Standard Edition, REBUILD takes a schema lock — do it in a maintenance window.
7. Run UPDATE STATISTICS ... WITH FULLSCAN more often than you think. Statistics drift causes more query regressions than fragmentation does.

Rebuild vs reorganize

Quick comparison:

Aspect	REBUILD	REORGANIZE
Rebuilds leaf pages	Yes	Yes
Rebuilds intermediate pages	Yes	No
Updates statistics	Yes (full scan)	No
Online (Enterprise)	Yes with ONLINE = ON	Always online
Online (Standard)	No (schema lock)	Always online
Memory required	High	Low
Can be interrupted	No (rolls back)	Yes (picks up next run)
Use it when…	Fragmentation > 30% AND you can afford the rebuild	Fragmentation 5-30%, online constraints

REORGANIZE doesn’t update statistics. If you run it, follow up with UPDATE STATISTICS on the same index. REBUILD updates stats for free.

Statistics beat fragmentation

Paul Randal has said this so many times it deserves to be the title of a book: update statistics first, worry about fragmentation second. Stats being out of date makes the optimizer pick bad plans, which slows queries by orders of magnitude. Fragmentation makes them maybe 10-20% slower on scans. Priorities.

-- Nuclear-from-orbit stats update
UPDATE STATISTICS Sales.Orders WITH FULLSCAN;

-- Everything in the database
EXEC sp_updatestats;

Ola’s scripts do this intelligently — they sample the tables that need it, skip the rest. Still the right call.

Page fill factor

FILLFACTOR tells SQL Server how full to make leaf pages when building or rebuilding an index. Default is 0 (treated as 100%). Lower fill factor leaves room on each page for future inserts/updates, reducing page splits.

CREATE NONCLUSTERED INDEX IX_Customer_Email
    ON Sales.Customer (Email)
    WITH (FILLFACTOR = 90);

Rule of thumb:

• Append-only tables (event logs, order history): FILLFACTOR = 100. No inserts in the middle; use all the space.
• Tables with heavy updates in middle pages (hot OLTP with non-sequential writes): 85-95%. Leaves room for updates without splits.
• Anything with GUIDs as a clustered key: stop, fix your schema (lesson 21), then think about fill factor.

Lower fill factor means more pages means more RAM used. Trade-off. Don’t set FILLFACTOR = 70 “to be safe” — you’re just wasting memory.

The SHRINK warning, because it comes up every month

Every junior DBA at some point runs DBCC SHRINKDATABASE “to reclaim space.” Don’t.

SHRINKDATABASE and SHRINKFILE move pages from the end of the file to the front, shuffling them arbitrarily. Every index they touch gets maximally fragmented in the process. You end up with:

• A smaller file (good).
• Terrible fragmentation on every large index (bad).
• Autogrowth events that make the file big again within days (sad).

Rule: never SHRINK production databases. If you need space, the right move is usually:

• Free up disk somewhere else.
• Archive / partition-switch-out old data first.
• Drop indexes that aren’t being used (lesson 22 has the query).
• As a last resort, SHRINKFILE (file, truncateonly) just trims free space at the end of the file without moving pages around.

When fragmentation actually matters

Real-world list:

• Large range scans on a disk-backed hot index. Logical fragmentation costs real I/O.
• Heavy read replicas where queries run on disk (never fully cached).
• GUID-clustered tables where every insert page-splits. Rebuilds help for a day; fix the clustered key instead.
• After a one-time big delete that leaves empty pages scattered across the index.

For everything else — typical Runehold-sized OLTP, SSD storage, RCSI on, covering indexes for hot queries — fragmentation is a minor concern. Focus on query plans and statistics, not the maintenance plan.

A sensible maintenance job

On Runehold’s prod server, the Agent job looks roughly like this:

• Daily, 03:00: UPDATE STATISTICS on tables modified since yesterday (using Ola’s IndexOptimize).
• Weekly, Sunday 01:00: Ola’s IndexOptimize on all user databases with sensible thresholds:
  • Indexes < 1,000 pages: skip.
  • 5-30% fragmented: reorganize.
  • 30%+ fragmented: rebuild.
  • All of this is Ola’s defaults, so it’s a one-liner.
• Monthly: full DBCC CHECKDB on every database (integrity check, not fragmentation).
• Never: SHRINKDATABASE.

Boring, effective, runs for years without attention.

Run this on your own machine

USE Runehold;
GO

-- 1. Survey your indexes
SELECT
    OBJECT_NAME(ips.object_id)                     AS table_name,
    i.name                                          AS index_name,
    ips.index_type_desc,
    ips.avg_fragmentation_in_percent               AS frag_pct,
    ips.avg_page_space_used_in_percent             AS fill_pct,
    ips.page_count
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 > 100
ORDER BY frag_pct DESC;

-- 2. Reorganize a specific index (online, low-impact)
ALTER INDEX IX_Customer_Email ON Sales.Customer REORGANIZE;

-- 3. Rebuild (Enterprise only to do it online)
ALTER INDEX IX_Customer_Email ON Sales.Customer
    REBUILD WITH (ONLINE = ON, FILLFACTOR = 90);

-- 4. Update statistics with full scan
UPDATE STATISTICS Sales.Customer WITH FULLSCAN;

-- 5. Do it all at once for one table (Ola-lite)
ALTER INDEX ALL ON Sales.Orders REBUILD WITH (ONLINE = ON, FILLFACTOR = 90);
UPDATE STATISTICS Sales.Orders WITH FULLSCAN;

Run the survey query on a server that’s been live for a while. Note how few of your indexes actually have meaningful fragmentation. Most shops I’ve visited have maybe five indexes that matter and fifty that don’t.

Module 4 is done. Indexes, covering, filtered, columnstore, fragmentation — all covered. You now have opinions about when to index and when to leave well enough alone.

Next: module 5, execution plans and performance. Lesson 25 (already written) is the main reading; lessons 26-28 go deeper into statistics, parameter sniffing, and Query Store.