I use Ola Hallengrens script for Index maintenance. Before I did that, I used the following query to see which indexes are fragmented most:
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
ORDER BY indexstats.avg_fragmentation_in_percent desc
In my case, the avg_fragmentation was over 70% for 15 indexes and over 30% for 28 indexes.
So, I rebuild every index using Ola Hallengren's solution. When I ran the query again, this was the result:
Fragmentation over 70% for 12 indexes, over 30% for 15 indexes.
I figured, the reason was because of the page_count
, which was lower than 1000 for each of the indexes that were still very fragmented. For example, one of the indexes with a
page_count
of 967 has a fragmentation percentage of 98,98%! To me, it seems worth rebuilding that index! I did, and afterwards, the fragmentation was 0%. Also, a index with a page_count
of 132 went from 95% to 0%
So, my question is, what reasons would there be to NOT rebuild those indexes? One reason might be that rebuilding costs time and resources, but because the indexes are small, doesn't this mean it costs relatively few resources and it would still be benfecial to rebuild it anyway?
There are multiple related question on this site, but all of them answer the question why a index would not defragment, or if indexes are still useful if they are small and you don't defragment them, whereas here the statement DOES decrease fragmentation, with the question being, why not do it anyway?