Why index rebuild does not reduce index fragmentatation

There may be 2 reasons for this. The first is that the index is less than 8 pages, and the second is that there is not enough space on the hard disk to rebuild.

Less than 8 pages

If an index is very small (less than 8 pages) it will use mixed extents. Therefore, it'll appear as if there is still fragmentation remaining. At such small indexes the fragmentation can 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.

Enough space for rebuild

This can also happen with very LARGE indexes. The issue was not enough contiguous free space inside the database to arrange the index consecutively. Also you can try with MAXDOP option like this;


To view fragmentation rate

SELECT dbschemas.[name] as 'Schema', dbtables.[name] as 'Table', dbindexes.[name] as 'Index'
, indexstats.avg_fragmentation_in_percent, indexstats.page_count
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS indexstats
INNER JOIN sys.tables dbtables on dbtables.[object_id] = indexstats.[object_id] 
INNER JOIN sys.schemas dbschemas on dbtables.[schema_id] = dbschemas.[schema_id] 
INNER JOIN sys.indexes AS dbindexes ON dbindexes.[object_id] = indexstats.[object_id] 
AND indexstats.index_id = dbindexes.index_id WHERE indexstats.database_id = DB_ID() 
ORDER BY indexstats.avg_fragmentation_in_percent desc

More detail : https://dba.stackexchange.com/questions/18372/why-index-rebuild-does-not-reduce-index-fragmentatation