Indexes still shows as fragmented after rebuild

[Former Member]

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

[Former Member]

Good morning

I was using an incrrect script to check for fragmentation on my indexes. I ran the followng and got the correct results:

SELECT SO.name AS TABLE_NAME,

SI.name AS INDEX_NAME,

SO.type_desc,

DMV.index_type_desc,

DMV.alloc_unit_type_desc,

DMV.index_depth,

DMV.index_level,

DMV.page_count,

DMV.avg_fragmentation_in_percent,

DMV.partition_number

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

LEFT OUTER JOIN sys.objects AS SO

ON DMV.OBJECT_ID = SO.OBJECT_ID

LEFT OUTER JOIN sys.indexes AS SI

ON DMV.OBJECT_ID = SI.OBJECT_ID

AND DMV.INDEX_ID = SI.INDEX_ID

WHERE alloc_unit_type_desc = 'IN_ROW_DATA'

AND index_level = 0

AND page_count > 1000

AND avg_fragmentation_in_percent > 10

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

I can strongly recommend the solution provided by Ola Hallengren as mentioned in my earlier post. It worked excellent in our SAP environment.

Regards

Wilhelm

[Former Member]

Hi

I am using a script from http://blog.ola.hallengren.com/blog/. It does the same thing, but no luck.

Wilhelm

[Former Member]

hi

try the script given in the following link

http://sqlserveradvisor.blogspot.com/2009/02/over-time-when-data-is-added-changed-or.html

schedule this as a database job

Rohit