Skip to Content
0
Former Member
Apr 15, 2009 at 10:42 AM

Indexes still shows as fragmented after rebuild

234 Views

Good day

I have determined the most fragmented indexes in my system with the following script:

SELECT

OBJECT_NAME(DMV.object_id) AS TABLE_NAME

,SI.NAME AS INDEX_NAME

,avg_fragmentation_in_percent AS FRAGMENT_PERCENT

FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'Detailed') as DMV

LEFT OUTER JOIN sys.indexes AS SI

ON DMV.OBJECT_ID = SI.OBJECT_ID

AND DMV.INDEX_ID = SI.INDEX_ID

WHERE avg_fragmentation_in_percent > 10

AND index_type_desc IN('CLUSTERED INDEX', 'NONCLUSTERED INDEX')

ORDER BY FRAGMENT_PERCENT DESC

I then do a rebuild of the indexes of these tables using the following command:

ALTER INDEX ALL ON [PEC].[pec].[<table name>] REBUILD WITH (SORT_IN_TEMPDB = OFF, ONLINE = ON)

The commands complete successfully. However, when I run the command as above again, the indexes still come up as fragmented. Why is this?

Thanks!

Regards

Wilhelm