Sql-server – IndexOptimize is successful, but sys.dm_db_index_physical_stats still shows huge fragmentation

fragmentationindex-maintenanceola-hallengrensql server

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?

Best Answer

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.

Related Solutions

Sql-server – Huge Size (still) with Differential Backup

That sounds perfectly expected. If you are updating most of your data and rebuilding all of your indexes: all the affected extents need to be in the differential backup, there is simply no way around that because it must contain every extent that has changed since the last full or differential backup.

If you are not already doing so add the COMPRESSION option to your backups, which can result in considerable savings (and speed up the backup process too by reducing IO).

Also if the size really is a massive issue, this might be one of the few occasions to question if a backup is needed particularly regularly. If you are regenerating all of the data in your warehouse database every day from other sources then you might not need to worry as much about backing it up as often as you do the source data, assuming of course that the source databases have good backup taking and testing processes in place, because you can reconstruct that data from the other sources (or backups thereof) anyway if disaster strikes. Of course this reconstruction process could be significantly slower than restoring a backup, so consult you users and your service restore time SLAs before changing the warehouse's backup plan.

Possible Remediation:

Two possible options to reduce the amount of data that you need to transfer spring to mind:

After each update, make a copy of the data without any non-clustered indexes (backup, restore locally, drop all non-clustered indexes, backup again and transfer this version) and recreate the indexes after restoring at the other end. This avoids transferring all the index data.
Make sure the daily changes are as minimal as possible by only INSERTing new rows and UPDATEing those that need it (leaving identical rows untouched), not rebuilding any indexes (this will mean not disabling them so may slow down the ETL process).
Depending on the complexity of your ETL process refactoring it this way may be a massive job. One way around this may be to add an extra stage to the end of the process: keep a second copy of the warehouse, rebuild the first using the normal process, then update the second using one MERGE statement per table to INSERT/UPDATE/DELETE as needed being very careful to leave any unchanged rows alone. Then take your differential backups from this DB. Keep minimal indexes in the primary instance of the copy, just keep the primary keys and clustered indexes (which you will want for efficiency of the MERGE operations) and rebuild the rest after restore.
This reduces the amount of no-op changes, so will hopefully markedly reduce the size of each differential, and reduce space in the backups devoted to index data.

Both these methods try to reduce the backup size that you are transferring at the expense of adding a fair chunk of extra processing at the receiving end (rebuilding indexes after restore) and sending end (maintaining the second copy at that location) so you are trading one bottleneck for another pair which may or may not be practical.

Depending on your data sources and the complexity of the ETL process you may have better luck with building the warehouse DB at both locations each time, as Chris suggested in the comments.

Sql-server – FillFactor: IndexOptimize script by Ola Hallengren

Ola's script will only change Fill Factor if you assign a value to the @FillFactor variable.

IF @FillFactor IS NOT NULL AND @CurrentIsPartition = 0 AND @CurrentIndexType IN(1,2,3,4) SET @CurrentCommand13 = @CurrentCommand13 + ', FILLFACTOR = ' + CAST(@FillFactor AS nvarchar)

Which means someone:

Modified the stored procedure to use 90 as a default value;
Ran it out-of-band from maintenance; or
Changed the values using a different script or the GUI.

You can script out the stored procedure to see if someone changed the default value,

Or you can query the dbo.CommandLog table to see if a REBUILD command was run with a Fill Factor of 90.

SELECT *
FROM dbo.CommandLog AS cl
WHERE cl.CommandType = 'ALTER_INDEX'
AND cl.Command LIKE '%FILLFACTOR%'

If someone changed it by using a different script, or the GUI, that would be much more difficult to track down.

Best Answer

Related Solutions

Sql-server – Huge Size (still) with Differential Backup

Sql-server – FillFactor: IndexOptimize script by Ola Hallengren

Related Question