To take this thread a little further, I have a problem reorganising the indexes on a particular table. This is the query I ran.....
SELECT object_id AS ObjectID,
index_id AS IndexID,
avg_fragmentation_in_percent AS PercentFragment,
fragment_count AS TotalFrags,
avg_fragment_size_in_pages AS PagesPerFrag,
page_count AS NumPages
NULL, NULL, NULL, NULL , 'DETAILED')
WHERE avg_fragmentation_in_percent > 30
and page_count > 1000
ORDER BY 3 desc;
.....and this is the output......
ObjectID IndexID PercentFragment TotalFrags PagesPerFrag NumPages
1295343679 0 76.4705882352941 719 10.3588317107093 7448
The number of pages is 7448 so a percentage fragmentation of 76.47 has meaning. Regardless of how often I reorg the index, the %fragmentation does not reduce and I am at a bit of a loss to understand why.
I would be grateful for any input you may have!
Thanks in advance.