Why index rebuild does not reduce index fragmentation

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;

ALTER INDEX IndexName ON TableName REBUILD WITH (MAXDOP=1)

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

Why index rebuild does not reduce index fragmentation

Comments

More from this blog

Response.Redirect: true or false?

Task.Run vs. QueueUserWorkItem

Optimizing LINQ Queries for SQL Server Bit Fields

Thread was being aborted

Could not load file or assembly X or one of its dependencies.

Less than 8 pages

Enough space for rebuild

To view fragmentation rate

Command Palette

Comments

More from this blog

Less than 8 pages

Enough space for rebuild

To view fragmentation rate