Why not rebuild indexes with page count <1000?

Question

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?

Answer 1

The guidance concerning the minimum number of pages is somewhat arbitrary. The biggest benefits of reducing fragmentation are:

1. It can improve read-ahead performance for large range scans; and
2. It may improve the page density (number of rows per page)

Both of these factors are less important for small indexes, by definition.

The counter-argument to rebuilding small indexes is essentially:

"Why bother? Don't you have more important things to worry about?".

That said, rebuilding/reorganizing is not free. It may be worth avoiding the extra effort and log generation in some cases (for example, if the log is shipped/copied across a WAN for any number of possible reasons - mirroring, availability groups, replication...). Also, unless (or even if, in some cases) you are rebuilding online, the rebuild may impact other concurrent processes through locking. Finally, for small indexes, rebuilding may not even reduce the fragmentation anyway, due to allocations from mixed extents (unless you are running with trace flag 1118 enabled).

If you still feel happier rebuilding these small indexes, and don't mind the consequences, then by all means change the value of the @PageCountLevel parameter passed to Ola's procedure.

See the PASS TV recording of Paul Randal's presentation on Index Fragmentation for all the details.

You might also like to watch Brent Ozar talk about Why Index Fragmentation doesn't matter in SQL Server.

Answer 2

Please see Paul Randal's post to index maintenance thresholds and where they come from: https://www.sqlskills.com/blogs/paul/where-do-the-books-online-index-fragmentation-thresholds-come-from/