Check SQL Server Index Fragmentation (and When It Doesn’t Matter) in 45 Seconds. The "45 Seconds DBA Series". Part 14
Before we dive into today's topic, if you missed my previous post you can take a look at Check Missing Indexes (and Why They Lie) in 45 Seconds. Execution Engine | Part 13.
👉 If you found this deep-dive helpful, feel free to check out the ads—your support helps me keep creating high-quality SQL Server content for the community.
Check SQL Server Index Fragmentation in 45 Seconds
Execution Engine | Part 14
Stop wasting maintenance windows on fragmentation that doesn't affect performance.
Most DBAs obsess over fragmentation percentages without realizing that modern storage and small table sizes make most defragmentation efforts useless. In this post, I will show you how to identify the only indexes that actually need your attention.
🧠 TL;DR BOX
✔️ Ignore small indexes: If an index has fewer than 1,000 pages, fragmentation is statistically irrelevant for performance.
✔️ SSD/NVMe Impact: Fragmentation affects sequential I/O; on modern flash storage, logical order matters significantly less than on spinning disks.
✔️ Rebuild vs Reorganize: Use 30% as a threshold for REBUILD and 5% to 30% for REORGANIZE, but only for large, active tables.
✔️ Internal Fragmentation: Low page density is often more damaging than logical fragmentation as it wastes Buffer Pool memory.
Hi SQL SERVER Guys and Gals,
We all know how important it is to make every second count when managing SQL Server environments. For decades, "defragmenting indexes" was the holy grail of maintenance. But the world has changed. With the advent of cloud storage, SSDs, and advanced read-ahead mechanisms, traditional fragmentation is often a "ghost problem." Let's dive into what actually matters in Part 14 of our series.
🔍 DIAGNOSIS
🧠 What it really is: Index fragmentation comes in two flavors: External (Logical Scan) Fragmentation, where the logical order of pages doesn't match the physical order, and Internal (Page Fullness) Fragmentation, where pages have too much empty space.
💣 The Problem: High external fragmentation slows down range scans because the disk head (on HDDs) has to jump around. High internal fragmentation means you are reading 8KB pages that might only be 50% full, effectively doubling your I/O and wasting precious RAM in the Buffer Pool. ⚡ Insights: SQL Server's Optimizer does NOT use fragmentation levels to decide on a plan. Fragmentation only affects the execution speed of that plan.
🧪 QUERY
Use this script to find indexes that are genuinely fragmented AND large enough to matter. We filter by page count to avoid "noise."
-- 🔍 Index Fragmentation Diagnostic Query (The "WOW" Header)
-- ⚡ Target: Indexes > 1000 pages with > 30% fragmentation
SELECT
DB_NAME() AS [DatabaseName],
OBJECT_NAME(ps.object_id) AS [TableName],
i.name AS [IndexName],
ps.index_type_desc,
CAST(ps.avg_fragmentation_in_percent AS DECIMAL(5,2)) AS [Fragmentation %],
ps.page_count,
ps.avg_page_space_used_in_percent AS [PageFullness %]
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'SAMPLED') ps
INNER JOIN sys.indexes i ON ps.object_id = i.object_id AND ps.index_id = i.index_id
WHERE ps.avg_fragmentation_in_percent > 5 -- Threshold
AND ps.page_count > 1000 -- 🚀 Crucial: Only care about larger indexes
ORDER BY ps.avg_fragmentation_in_percent DESC;
🚀 FIX
Once you identify a high-impact fragmented index, apply the following strategy:
- ✔️ REORGANIZE: For fragmentation between 5% and 30%. It’s an online operation and doesn't require significant lock overhead.
- ✔️ REBUILD: For fragmentation > 30%. This recreates the index. Use ONLINE = ON (Enterprise Edition) to avoid blocking users.
- ✔️ Fill Factor: If an index fragments daily, consider a Fill Factor of 80-90% to leave room for new inserts.
🚀 My REAL Strategy
In my 25 years of experience, I’ve seen more performance issues caused by maintenance windows blocking users than by fragmentation itself. My Rule: If you are on NVMe storage, ignore external fragmentation up to 60%. Focus instead on Page Fullness. If your pages are only 50% full, you are paying for twice the storage and twice the memory. Rebuild to pack those pages and save your Buffer Pool!
📊 TAKEAWAY
Fragmentation is a physical storage issue, not a logical query issue. Check your indexes in 45 seconds using the DMV query above, filter for size, and only fix what is actually costing you money or performance. Focus on the big fish.
📢 Support the Blog: Did you find this deep-dive helpful? The ads you see here are selected to reflect your interests. If a partner's offer catches your eye, give it a look! Your engagement helps me continue publishing premium SQL Server content for the community.
Biondi Luca @2026 - Sharing over 25 years of Gained Knowledge for Passion. Share if you like my posts!
🧠 Master SQL Server Performance Hub
Expand your knowledge with my latest deep-dives and performance guides.
Performance "45 Seconds"
Indexing & Tuning
Execution Plans
Don't forget to check my April 2026 Recap for a complete monthly summary.
Comments
Post a Comment