What is index fragmentation, and how do you fix it?
Quick Answer
Fragmentation is when index pages are out of physical order (external) or partly empty (internal), usually from page splits caused by inserts/updates. It makes range scans do more I/O. Fix it by REORGANIZE (light, online, for ~5–30% fragmentation) or REBUILD (heavier, rebuilds the index, for >30%).
As data changes, index pages get inserted into and split, so they end up physically out of order or half empty — that's fragmentation, and it means range scans read more pages than they should. I check sys.dm_db_index_physical_stats, and the rule of thumb is REORGANIZE for light fragmentation and REBUILD for heavy, above about 30%. REBUILD also refreshes the statistics with a full scan. I'd also mention that on SSDs fragmentation matters much less than people think — out-of-date statistics are usually the bigger performance issue.
- Check with
sys.dm_db_index_physical_stats—avg_fragmentation_in_percentandpage_count(ignore tiny indexes under ~1000 pages). - REORGANIZE — defragments the leaf level in place, always online, can be stopped and resumed.
- REBUILD — drops and recreates the index, also rebuilds statistics with a full scan; online rebuild needs Enterprise (or has short locks otherwise).
- Fill factor — leave free space on each page (e.g. 90%) so inserts don't split pages as often; costs space and read I/O.
- On SSDs and with modern SANs, fragmentation matters far less than it used to — don't over-tune it; stale statistics are usually the bigger problem.