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?


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


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.

| improve this answer | |
  • Can't believe that I missed this information! Thanks for the heads up, can confirm that all the indexes still fragmented at < 1000 pages. – Tom_W Nov 29 '19 at 9:16

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.