Sample Header Ad - 728x90

Why not rebuild indexes with page count <1000?

22 votes
3 answers
34856 views
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?
Asked by user1261104 (415 rep)
Jan 2, 2015, 10:22 AM
Last activity: Jun 5, 2025, 11:06 AM