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.
Sql-server – Why index REBUILD does not reduce index fragmentatation
clustered-indexfragmentationindexsql server
Related Question
- Sql-server – index rebuild/reorganize frequency
- Sql-server – Why not rebuild indexes with page count <1000
- Sql-server – REORGANIZE seems to work as well as REBUILD all the time
- Sql-server – Does index rebuild time depend on the fragmentation level
- Sql-server – Rebuild index in SQL Server does not reduce fragmentation
- Sql-server – Online & Offline Index Rebuild
- Sql-server – How big of a table or how many data pages in an index is worth considering running index rebuild or index reorganize operations on
Best Answer
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.