I’ve been working with AGs for the last year and have a couple of things as regards indexing that I thought would be good to share:
Always sort in tempdb
When you create, or rebuild an index you have the ability to have the index perform the sort of the data inside of tempdb. This greatly reduces the amount of work that has to be done within the user database, and greatly decreases the volume of log work required. This is of enormous benefit when using the synchronous commit mode as it reduces the impact of the process. There’s nothing worse than having your AG get really behind with HADR_SYNC_COMMIT waits because you are creating/rebuilding an index inside the database.
To not use the user database for sorting your data include the SORT_IN_TEMPDB=ON syntax within your create/rebuild statement.
Don’t do reorgs
This is another one that I’ve seen cause serious damage on the sync commit front. Reorganizing pages is generally something you do with indexes that have a lower level of fragmentation. In every AG that I’ve attempted to use this I have run into problems. It ends up causing high HADR_SYNC_COMMIT waits as it works on the page by page changes. This is especially true if you are running with fast storage (PCIe flash, or high performance SSD).
To get around this, do not perform reorgs of your data. I’ve found rebuilds (with a tempdb sort) to have far less impact on an AGs performance than a simple reorg of a larger table.
Consider going async
This one might seem like some strange advice, but if you have a high performing system and are worried about impact, and feel that your business can accept the risk for potential data loss of a few seconds (you’ll need to figure out what the acceptable level of loss is with your particular company and application), then consider going to the asynchronous commit mode on your secondaries so that you don’t run into any problems on index work. While not ideal, this could be something that the business could accept to help ensure that the application still functioned with a level of performance while your index maintenance goes on.
Index maintenance is a critical part of SQL Server to help you keep your system running its best. If you don’t have a solid routine (or are still using maintenance plans), then consider using the MidnightSQL Minion Reindex solution. It is highly configurable, and as a part of it you can do things like ensuring you always sort in tempdb, never do reorgs, or even set your AGs to async, and then back to sync when you are done.
SirSQL.net 
Thanks for this article, Nicolas. I’ve been experiencing performance degradation due to HADR_SYNC_COMMIT waits during Index Maintenance.
With regard to going from SYNC to ASYNC, are you saying that you go from SYNC to ASYNC, then run index maintenance, and then go back to SYNC? If so, is there usually a performance hit when going back to SYNC? I’m thinking the answer is “it depends” on how far behind the replicas are when we decide to go back to SYNC mode, and we should probably write something to check latency prior to setting back to synchronous mode. Am I on the right track? Any other things I’m missing or new things you’ve learned about this subject in the 15 months since this was written?
Thanks for blogging!
LikeLike
Sadly nothing new Stephen. Well, except that 2016 mostly fixes this as an issue (for some larger tables not quite, but the improvements are more than dramatic).
It depends is absolutely the answer, but what you could do, as a part of your reindexing routine is to look at the last_redone_time in sys.dm_hadr_database_replica_states and when it’s caught up to the last_commit_time on your primary (or within a couple of seconds) then you should be good to set things back to SYNC once more.
This being said, there’s no inherent perf hit by switching back to SYNC beforehand. It will run in an pseudo SYNC mode until the secondary is caught up (whereby it might show as SYNC, but actually still be ASYNC in the background). It’s worth experimenting with in your environment to see how things play out with your application.
LikeLike
Thanks so much for the quick response, Nicholas. I’m new to AGs so I was not sure if I needed to wait for the SYNC to catch up, first. I spoke to someone, yesterday, who advised the same…that changing back to SYNC would still run in ASYNC until caught up. That makes it easier.
It’s good to hear that 2016 mostly fixes this issue. We are on track to migrate to 2016 relatively soon. This will encourage us to move faster.
LikeLiked by 1 person