Index Maintenance and Availability Groups

I have a very large database which I migrated to a virtual server and then implemented AlwaysON availability groups. It’s a 3 node cluster with one Primary (obviously), a Synchronous read-only secondary and an asynchronous non-readable node in a DR data centre.

One thing that was very noticeable after the migration was the duration of my index optimisation jobs went through the roof. Indexing was originally set up to only run at the weekends by a previous DBA. Admittedly the database is large but I was in a situation where the job would start on a Friday night and it wouldn’t have completed by Monday and it was conflicting with all other maintenance as well as the backups! Ola Halengren’s index maintenance script was being used but it was set using the default parameters.

The first thing I did was  change @fragmentationlevel1 to ‘30’ and @fragmentationlevel2 to ‘60’ to see if this at least sped up the process. It did but not by much. I seemed to be in a state of catch up and the indexes never appeared to be optimal. So I then set the @TimeLimit so it ran each night but stopped well before backups and busy periods. But it would still run for the whole period and indexing was still a long way from completion. It seemed to be the equivalent of painting the Forth rail bridge.

It appeared that this wasn’t uncommon with databases in availability groups. This coupled with the index reorganisations meant my log was under much more pressure than it used to be before being part of an AG. So to relieve the pressure on the database transaction log, I configured the indexing job with the following parameters:

@SortInTempdb = ‘Y’ – The user database workload will be decreased. Tempdb is normally a bit quieter outside office hours.

@FragmentationMedium = ‘INDEX_REBUILD_ONLINE, INDEX_REBUILD_OFFLINE’. Again, rebuilds are not heavily using the transaction logs like a reorganization does. This Also improve the speed of synchronization and reduced the HADR_SYNC_COMMIT waits.

From the first night this made a dramatic difference, certainly with duration of the job.Just to double-check, I ran the following script to confirm that my fragmentation levels were under control.

SELECT dbschemas.[name] as 'Schema',
dbtables.[name] as 'Table',
dbindexes.[name] as 'Index',
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
WHERE indexstats.database_id = DB_ID()
AND indexstats.page_count > 1000
and indexstats.avg_fragmentation_in_percent > 0
ORDER BY dbtables.[name] desc


I was suddenly in a position where my Indexing jobs were now finishing within an hour during the weekdays. This allowed me to change  my defrag levels back down during the week to ‘20’ and ‘50’ and move them higher again at the weekend and still have time left to spare.

I have heard lots of DBAs say they would never rebuild all indexes, especially at low levels of fragmentation and it many factors can play a part in this decision (If only I got a pound for every time I answer an SQL configuration question with the answer ‘It Depends’ etc), but in this case, rebuilding these indexes that would normally be reorganized has made a dramatic improvement.

Also, to improve AG performance and there being less chance of a re-seed failure if one of my nodes went down (the log will continue to grow until it can commit the data), I re-sized the transaction log and increased log backups to every 15 minutes from 30 minutes.

Now I need to decide what do with all that spare time I now have on my overnight job schedule.