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 (
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
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.