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.