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.
Continue reading on SirSQL.net.