Every SQL Server DBA eventually faces the same call during a maintenance window: rebuild or reorganize a fragmented index? It sounds like a small technical choice. In practice, it affects how much I/O your server absorbs, how long your maintenance window runs, and whether your transaction log balloons in the middle of a business day.

This guide walks through what fragmentation actually is, the thresholds most teams use as a starting point, when reorganize is the right call, when rebuild is worth the cost, and where the textbook advice starts to fall apart in real production environments.

What Index Fragmentation Actually Means

Index fragmentation happens when the logical order of pages in an index no longer matches their physical order on disk. SQL Server builds and maintains indexes as balanced tree (B-tree) structures. As rows are inserted, updated, and deleted, pages fill up and split, and over time the physical layout drifts away from the logical order the index was designed around.

The practical effect is more I/O. Instead of reading a handful of contiguous pages for a range scan, SQL Server has to jump around the file, and each jump costs time. On busy OLTP systems, that overhead compounds fast.

The Fragmentation Tiers DBAs Actually Use

Most teams work from a three-tier model when triaging fragmentation:

●      Healthy – under 20% fragmentation. Generally left alone.

●      Watch – 20% to 30% fragmentation. Reorganize is the typical response.

●      Critical – above 30% fragmentation. Rebuild is the typical response.

These thresholds originated with Microsoft’s own guidance on reorganizing and rebuilding indexes and are still a reasonable starting point. But they were written with spinning disk I/O patterns in mind, and they don’t account for index size. A three-page index sitting at 80% fragmentation is not a real problem – there simply isn’t enough data there for fragmentation to matter. Page count and row count deserve as much attention as the fragmentation percentage itself.

Reorganize: What It Does and When It’s Right

Reorganizing an index defragments the leaf level in place. It’s a lighter-weight, always-online operation that doesn’t rebuild the index structure from scratch. It’s the right call for moderate fragmentation where you want to reclaim some order without paying the full cost of a rebuild, particularly on large tables where a full rebuild would eat into your maintenance window or generate more transaction log activity than you can absorb.

The tradeoff is that reorganize is single-threaded and, on heavily fragmented, large indexes, it can take considerably longer than a rebuild to reach the same result. It may not deliver the query improvements you expect.

Rebuild: What It Does and When It’s Right

Rebuilding drops and recreates the index. It fully resolves fragmentation, updates statistics with a full scan as a side effect, and gives you the opportunity to adjust fill factor at the same time. It’s the right call once fragmentation crosses into critical territory, or when you need statistics refreshed alongside the fragmentation fix.

The cost is real: rebuilds are more resource-intensive, generate significantly more transaction log activity (especially in the full recovery model), and – depending on SQL Server edition and table size – may require an offline operation that briefly locks the table.

Where the Standard Advice Breaks Down

The DBA community has spent the last several years pushing back on the idea that fragmentation percentage alone should drive the decision. A well-known line of research from SQL Server performance specialists has shown that on modern SSD and NVMe storage, the read-performance penalty from fragmentation is far smaller than it was on spinning disk – meaning some indexes flagged as critical under the old thresholds aren’t actually causing measurable slowdowns.

There’s a second, related problem: blind automation. Maintenance plans that rebuild every index above a fixed threshold, on a fixed schedule, without accounting for index size, workload pattern, or actual page splits, can end up doing more harm than good – burning I/O and log space on tables where fragmentation was never the real issue in the first place.

The practical takeaway is that fragmentation percentage should be one input into the decision, not the only one. Page count, table size, storage type, and workload pattern all belong in the equation.

Making the Decision Without the Manual Guesswork

This is exactly the kind of decision that benefits from policy-driven automation rather than a fixed script run on a schedule. Nibiru Software’s SQL Database Defragmenter evaluates fragmentation, index type, cardinality, and page count together, then applies reorganize or rebuild based on policies you define – rather than a single blanket threshold. Fill factor adjustment, statistics updates, and stored procedure recompilation are handled as part of the same policy, and none of it requires taking the server offline.

If you want to see how the fragmentation triage looks in practice, the Web Dashboard breaks down Critical, Watch, and Healthy indexes at the server and database level so you’re never guessing which objects actually need attention.

Key Takeaways

●      Under 20% fragmentation: generally leave it alone.

●      20% to 30%: reorganize.

●      Above 30%: rebuild.

●      Always weigh page count and index size alongside the percentage – small indexes rarely need action regardless of their fragmentation reading.

●      Fixed-threshold automation without context can waste I/O and log space; policy-based automation that considers multiple factors performs better in production.

Related reading on nibirusoftware.com

What Is MDF File Fragmentation and Why Most DBAs Miss It – covers the separate, often-overlooked problem of physical database file fragmentation.

How to Check SQL Server Index Fragmentation Using DMVs – the T-SQL you need to identify fragmented indexes before deciding how to fix them.

See also: SQL Database Defragmenter Dashboard and Free Trial pages on nibirusoftware.com.