Rethinking Index Maintenance: Why avg_fragmentation_in_percent Is Outdated and What You Should Do Instead

As a SQL Server DBA with years of experience tuning production environments, I’ve seen the same patterns repeat: nightly index maintenance jobs running for hours, consuming massive CPU and I/O, only for performance to remain inconsistent or even degrade in some cases. Many of us (myself included, early in my career) relied heavily on avg_fragmentation_in_percent from sys.dm_db_index_physical_stats to decide when to reorganize or rebuild indexes. It’s time we move past that outdated approach and stop just doing what we’ve always done.

The Problem with Traditional Fragmentation Metrics

avg_fragmentation_in_percent measures logical (or external) fragmentation which is how out-of-order the index pages are on disk. Microsoft’s older guidance suggested reorganizing at 5-30% and rebuild above 30%. Ola Hallengren’s excellent maintenance scripts adopted similar defaults because they were the best available at the time.

In modern environments, especially with SSDs, large memory footprints, and Azure/SQL Server advancements, this metric often misleads us. High logical fragmentation doesn’t always hurt performance if pages are cached or if read-ahead works efficiently. Conversely, low page density (internal fragmentation) wastes memory and I/O because pages hold fewer rows of data than they could.

Microsoft’s current documentation emphasizes this shift: increasing page density often delivers greater performance gains than simply reducing logical fragmentation. Look at avg_page_space_used_in_percent, this tells you how full your pages actually are. Low density means more pages to scan, more memory pressure, and higher I/O. If you focus on lower density indexes with a higher page count, you can target those specifically and decrease unneeded IO from excessively rebuilding/reorganizing indexes.

In practice, I’ve found indexes with “high” fragmentation (say 40-60%) that perform fine because their pages are densely packed. Meanwhile, indexes sitting at 20% fragmentation but only 60-70% page density can cause noticeable slowdowns on range scans or lookups.

Better Monitoring: Focus on Page Density

Start querying both metrics together:

SELECT 
    OBJECT_NAME(ips.object_id) AS TableName,
    i.name AS IndexName,
    ips.avg_fragmentation_in_percent,
    ips.avg_page_space_used_in_percent,
    ips.page_count
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'SAMPLED') ips
INNER JOIN sys.indexes i ON ips.object_id = i.object_id AND ips.index_id = i.index_id
WHERE ips.page_count > 1000  -- Ignore tiny indexes
ORDER BY ips.avg_page_space_used_in_percent ASC;  -- Prioritize low density

Prioritize maintenance on indexes with low avg_page_space_used_in_percent (e.g., below 70-80%) and sufficient page count. This approach targets real bloat and waste more effectively than chasing logical fragmentation numbers.

Updating Ola Hallengren’s IndexOptimize: Practical Recommendations

Ola Hallengren’s scripts remain the gold standard for most environments, far superior to built-in maintenance plans. But the defaults can lead to excessive reorganizations, which provide limited benefit and can actually contribute to more fragmentation over time in some workloads.

Recommended modifications for @FragmentationMedium and related parameters:

Raise thresholds: Set @FragmentationLevel1 = 30 (or higher) and @FragmentationLevel2 = 60-70.
Drop or minimize INDEX_REORGANIZE. Many experts now recommend skipping reorganize entirely, it can be inefficient and doesn’t always improve page density as well as a targeted rebuild. Focus on online rebuilds where possible. If you are a 24/7 shop with Standard Edition, rebuilds are an offline operation, so reorganizations may be your best and only option.

Use @PageCountLevel = 5000 or higher to skip small indexes. Many industry consultants recommend starting at 50000 pages.
Add time limits (@TimeLimit) and consider MAXDOP controls for large environments.

Example call:

EXEC dbo.IndexOptimize 
    @Databases = 'USER_DATABASES',
    @FragmentationLow = NULL,
    @FragmentationMedium = 'INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
    @FragmentationHigh = 'INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
    @FragmentationLevel1 = 30,
    @FragmentationLevel2 = 70,
    @PageCountLevel = 5000,
    @SortInTempdb = 'Y',
    @TimeLimit = '3600',
    @MaxDOP = 4;  -- Adjust to your workload

This reduces unnecessary work dramatically while focusing effort where it matters. Test in a non-production environment first, and monitor for resumable index operations in newer SQL Server versions. TimeLimit is in seconds so adjust as needed.

Don’t Forget Statistics – Often More Important Than Defrag

Index maintenance and statistics updates are related but distinct. Rebuilds update stats automatically, but reorganizes do not, and many indexes never hit high enough fragmentation thresholds to trigger updates. Outdated statistics lead to poor cardinality estimates and bad query plans, frequently a bigger performance killer than moderate fragmentation.

Guidelines I recommend:

Update statistics daily with default sampling (or moderate SAMPLE rate) for volatile tables. This keeps plans fresh without massive overhead.
Perform a full scan weekly (or during maintenance windows) for key tables or those with known data distribution changes. You want to avoid full scans daily on large tables as it can cause significant I/O spikes.

Use Ola’s IndexOptimize with a separate statistics job, or combine thoughtfully and target specific tables rather than blanket updates.
You should also monitor with sys.dm_db_stats_properties or Extended Events for stale stats.

In many cases I’ve troubleshot, refreshing statistics resolved “mysterious” slowdowns faster than any index rebuild.

Wrapping it up

Index maintenance isn’t about blindly following the old 5/30 rule or running jobs “because that’s what we’ve always done.” Focus on page density, tune Ola’s scripts to be smarter and less aggressive, and prioritize statistics updates. This approach reduces maintenance windows, lowers resource usage, and delivers better real-world performance.

Monitor your workloads, baseline before and after changes, and adjust per database, there’s no one-size-fits-all. Also remember that workloads and data sizes change over time, so this isn’t a ‘set it and forget it’ thing. You may have to tweak and tune these values for your databases over time.