IndexOptimize is successful, but sys.dm_db_index_physical_stats still shows huge fragmentation

Question

I'm working on a project that requires a database shrink. Following the shrink, I checked sys.dm_db_index_physical_stats and can see a lot of my indexes are over 99% fragmented (avg_fragmentation_in_percent).

In response to this, I executed IndexOptimize by Ola Hallengren, with the following code:

EXECUTE [dbo].[IndexOptimize]
    @Databases = 'Dynamic.Migrate',
    @FragmentationLow = 'INDEX_REORGANIZE',
    @FragmentationMedium = 'INDEX_REBUILD_OFFLINE',
    @FragmentationHigh = 'INDEX_REBUILD_OFFLINE',
    @FillFactor = '80',
    @UpdateStatistics = 'ALL',
    @StatisticsSample = '100',
    @LogToTable = 'Y'

This completed successfully, but when I check sys.dm_db_index_physical_stats again, the avg_fragmentation_in_percent values have not changed, and state my indexes are still fragmented by over 99%.

Am I missing something here?

Answer 1

This is from Ola Hallengren's SQL Server Index and Statistics Maintenance documentation:

MinNumberOfPages

Set a size, in pages; indexes with fewer number of pages are skipped for index maintenance. The default is 1000 pages. This is based on Microsoft’s recommendation.

IndexOptimize checks page_count in sys.dm_db_index_physical_stats to determine the size of the index.

Therefore, the indexes you mentioned are still fragmented after executing IndexOptimize were most likely skipped for having fewer pages than the minimum of 1000 pages since you didn't change that value in your code.