Why Index Fragmentation Remains High
In one of companies I worked for, we used to do nightly index rebuilds. What was baffling is even after the nightly rebuilds, the fragmentation for some of the tables remain high.
I did some digging; here’s an explanation on why some index fragmentations remain high.
Both Paul Randal and Kalen Delaney (highly respected, very reputable SQL Server developers/trainers) suggest to rebuild the index only if there’s at least 1000 pages affected. If < 1000 pages, they say fragmentation will not really be affected and potentially would remain high.
I checked the "questionable" tables in our databases, and yes, Paul and Kalen were right on the ball. These pages had <1000 pages each.
- tableA has 9 pages and initial fragmentation of 875. After rebuilding, fragmentation flip flops between 66% and 77%, and never improves beyond 66%
- tableB has almost 95000 pages and initial fragmentation of 69%. After rebuilding indexes, fragmentation drops to 0.01%. Succeeding rebuilds keep fragmentation to 0.01%
This makes sense, because for smaller tables an index will not really help – SQL Server will usually prefer to do a table scan.
I will post the script that I used to determine and apply selective index rebuilds.
Follow Up: Script is posted in