Index Fragmentation and Statistics Maintenance in SQL Server: REORGANIZE vs REBUILD

“Always rebuild indexes weekly” is one of the most common DBA myths in this entire field. Here’s what fragmentation and statistics actually require.

Measuring Fragmentation First

This is the direct extension of Ch.8’s index-usage DMV lesson — same evidence-first instinct, now pointed at internal index health instead of usage frequency.

SELECT OBJECT_NAME(ips.object_id) AS table_name, i.name AS index_name,
    ips.avg_fragmentation_in_percent, ips.page_count
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'LIMITED') ips
JOIN sys.indexes i ON i.object_id = ips.object_id AND i.index_id = ips.index_id
WHERE ips.avg_fragmentation_in_percent > 5
ORDER BY ips.avg_fragmentation_in_percent DESC;

The Standard Thresholds

The Myth, Directly Addressed

A small, rarely-scanned table can sit at 60% fragmentation and never matter, because it’s tiny and always read via seeks. Fragmentation only meaningfully affects large tables read via range scans. Evidence-first applies here too: measure, don’t schedule blindly.

Statistics: Often the Real Culprit

-- Check when statistics were last updated and how many rows have changed since
SELECT OBJECT_NAME(s.object_id) AS table_name, s.name AS stats_name,
    sp.last_updated, sp.rows, sp.modification_counter
FROM sys.stats s
CROSS APPLY sys.dm_db_stats_properties(s.object_id, s.stats_id) sp
WHERE OBJECT_NAME(s.object_id) = 'YourTableName';

-- Manually refresh if auto-update hasn't triggered recently
UPDATE STATISTICS dbo.YourTableName WITH FULLSCAN;

By default, auto-update statistics fires after roughly 20% of rows change (with some newer, more granular thresholds on recent SQL Server versions for large tables). A query that suddenly gets a bad plan after a large bulk load is very often stale statistics, not fragmentation — check this DMV before reaching for a rebuild.