Let’s Say You’re Offloading
Because you’ve got the cash money to pay for Enterprise Edition, some nice hardware, and also Enterprise Edition on another server or two.
Maybe you have queries that need fresh data going to a sync replica, and queries that can withstand slightly older data going to an async replica.
Every week or every month, you want to be a dutiful data steward and see how your indexes get used. Or not used.
So you run Your Favorite Index Analysis Script® on the primary, and it looks like you’ve got a bunch of unused indexes.
Can you drop them?
Not By A Long Shot
You’ve still gotta look at how indexes are used on any readable copy. Yes, you read that right.
DMV data is not sent back and centralized on the primary. Not for indexes, wait stats, queries, file stats, or anything else you might care about.
If you wanna centralize that, it’s up to you (or your monitoring tool) to do it. That can make getting good feedback about your indexes tough.
Failovers Also Hurt
Once that happens, your DMV data is all murky.
Things have gotten all mixed in together, and there’s no way for you to know who did what and when.
AGs, especially readable ones, mean you need to take more into consideration when you’re tuning.
You also have to be especially conscious about who the primary is, and how long they’ve been the primary.
If you patch regularly (and you should be patching regularly), that data will get wiped out by reboots.
If you use SQL Server’s DMVs for index tuning (and really, why wouldn’t you?), you need to take other copies of the data into account.
This isn’t just for AGs, either. You can offload reads to a log shipped secondary or a mirroring partner, too.
Perhaps in the future, these’ll be centralized for us, but for now that’s more work for you to do.
Thanks for reading!