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.
Now what?
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!
Going Further
If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 75% discount to my blog readers if you click from here. I’m also available for consulting if you just don’t have time for that and need to solve performance problems quickly.
I’ve run into this problem (and a few other around the nuances of AGs) and addressed it with a little powershell script: https://github.com/RowdyVinson/Powershell-Tools/blob/master/ExportPlans.ps1
I had a problem where new sprocs were being added but tests were not being expanded to cover them, so I needed to be able to comparetests executions vs prod executions. the *-proclist.txt” files cover that. There was also the trouble around stale testing data generating different plans than live prod data, thus I export every plan (*.sqlplan files) in cache that I can get my hands on, so I can compare them later if something is amiss. Last, In order to figure out which indexes are being used across the cluster, I grab all the used index data out of the execution plans and write that off to a list (the IndexesUsed.csv). If you execute this across all nodes in an AG, it can give you a pretty solid view of that’s going on. That said, I know this doesn’t cover everything. There are some non-cached plans, edge-case index use, and likely a few other things this would miss.