Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have used ALTER INDEX REBUILD to remove index fragmentation. In some cases REBUILD does not seem to remove this fragmentation. What are the reasons why REBUILD does not remove fragmentation? It seems that this happens especially with small indices.

share|improve this question
4  
How small? Less than 8 pages? The first 8 pages are allocated from mixed extents rather than uniform extents. –  Martin Smith May 24 '12 at 18:08
    
add comment

2 Answers

up vote 12 down vote accepted

If an index is very small (I believe less than 8 pages) it will use mixed extents. Therefore, it'll appear as if there is still fragmentation remaining, as the housing extent will contain pages from multiple indexes.

Because of this, and also the fact that in such a small index that fragmentation is typically negligable, you really should only be rebuilding indexes with a certain page threshold. It is best practices to rebuild fragmented indexes that are a minimum of 1000 pages.

share|improve this answer
add comment

This can also happen with very LARGE indexes.

I had some indexes on a table with around 700m rows that I couldn't defragment below around 30%. The issue was not enough contiguous free space inside the database to arrange the index consecutively.

To work around a very large index that won't defragment, the BEST solution is to pre-size a new database and move all your objects to that DB, then recreate your indexes there.

share|improve this answer
    
Great point, +1. –  Thomas Stringer May 24 '12 at 19:42
2  
+1 because the sparkle of the diamond caused me to blink –  Aaron Bertrand May 24 '12 at 21:13
    
@AaronBertrand Hahahahaha Nice! JNK is a mod! –  Thomas Stringer May 24 '12 at 22:08
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.