Loading

Why index REBUILD does not reduce index fragmentation?

N Shashidhar Reddy

7y1.9k

Hi,

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?

2 Replies

Know the answer? Post it — somebody with the same question will find it here.

Nirav Gandhi7y ago
If an index is very small (less than 8 pages) it will use mixed extents.
Because of this,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. or you can follow below rules:-
IF @FragmentationThreshold between 30 to 50 percent you can Reorganize index
IF @FragmentationThreshold > 50 percent you can Rebuild index
0
Subbarao A7y ago
Hi

Below link will help on the index fragmentation

https://blog.sqlauthority.com/2010/01/12/sql-server-fragmentation-detect-fragmentation-and-eliminate-fragmentation/
+1