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