Failover in Availability Group wipes data from sys.dm_db_index_usage_stats

Question

Failover in Availability Group wipes data from sys.dm_db_index_usage_stats

askcoffman

Mr or Mrs. 500

Points: 581
More actions
April 5, 2023 at 6:52 pm

#4169652

Im using Brent's sp_blitzIndex results and storing them to prevent any loss during a restart. I found that when a failover occurs on a 2016 and 2019 AG that this table gets reset on the server that was Primary and is now a secondary. Is this expected? My assumption was that these tables only lost data during a sql service restart.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Grant Fritchey SSC Guru Points: 398909 More actions · Answer 1

Several different things reset that information. It's part of why when someone says they're looking to remove unused indexes, I suggest caution when referencing that stuff as a way of determining the indexes in use. It's not bad to reference this data, in fact it's a great resource, but knowing it's not a flawless set of information is good to know.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

askcoffman Mr or Mrs. 500 Points: 581 More actions · Answer 2

Thanks for the reply Grant. I agree using this data must be used with caution. My goal now is to make the stored results more aware of these situations that might zero out the usage tables, giving more confidence in the metrics. I'll post the script once its complete.

Jeff Moden SSC Guru Points: 1004414 More actions · Answer 3

askcoffman wrote:

Thanks for the reply Grant. I agree using this data must be used with caution. My goal now is to make the stored results more aware of these situations that might zero out the usage tables, giving more confidence in the metrics. I'll post the script once its complete.

My recommendation when you use a completely open source tool, like the fine code that Brent offers, that you take the time to look at the code, find the tables and views it uses, and then lookup the MS documentation to find out which tables get reset as a part of a service restart and expected lifetime of data (some stuff may live in cache for only moments).

It takes some extra time but you can learn a lot by doing that and maybe even figuring out what his code is doing.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)