Index fragmentation remains one of the most common causes of degraded query performance in production SQL Server workloads. As rows are inserted, updated, and deleted, leaf-level pages split and fill unevenly, increasing logical reads and slowing seeks and scans.

The solution is not daily full rebuilds but a measured approach that monitors actual fragmentation, applies the right operation at the right threshold, and respects recovery model and availability constraints.

#Understand Fragmentation Thresholds

Rebuild when avg_fragmentation_in_percent exceeds 30 percent and the index contains more than 1,000 pages. Reorganize between 10 and 30 percent. Below 10 percent, leave the index alone.

tsql 
SELECT OBJECT_NAME(i.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, 'DETAILED') ips
JOIN sys.indexes i ON ips.object_id = i.object_id AND ips.index_id = i.index_id
WHERE ips.avg_fragmentation_in_percent > 10
  AND ips.page_count > 1000
ORDER BY ips.avg_fragmentation_in_percent DESC;

#Choose the Right Maintenance Operation

  • Use ALTER INDEX ... REBUILD for severe fragmentation or when you need to change fill factor or partition scheme.
  • Use ALTER INDEX ... REORGANIZE for moderate fragmentation; it is online and uses less log space.
  • Avoid rebuilding every index on a fixed schedule; base decisions on measured fragmentation.

#Automate with Minimal Impact

Schedule maintenance during low-activity windows or use the ONLINE option when running Enterprise Edition. For Standard Edition, consider partitioning large tables so only affected partitions are touched.

Log the start and end time, the operation performed, and the resulting fragmentation level. This data helps refine thresholds over time and demonstrates the value of the maintenance routine to operations teams.

#Practical Takeaway

Measure fragmentation weekly, rebuild only when necessary, and prefer reorganize for moderate cases. This pattern keeps SQL Server indexes healthy while controlling both CPU and transaction log impact in production.