Interpreting Index Usage Stats on an Availability Group

• data.jedi.ken

  Valued Member

  Points: 62

  More actions

  December 2, 2020 at 1:50 pm

  #3816025

  I've been querying sys.dm_db_index_usage_stats on the nodes of an availability group, and I've noticed an exponential difference between the number of user_seeks on the primary node versus either of the secondary nodes. On the table I'm analyzing, the primary node has many indexes with user_seeks greater than 100,000, whereas on the secondary nodes not one of those same indexes ever exceeds 100 user_seeks. I'm wondering if this is normal behavior or not.

  I'm wondering if either of the secondary nodes was restarted. Another thought is perhaps there's nothing in our applications directing read queries to the secondary nodes. (I'm referring to the "ApplicationIntent=ReadOnly" parameter of the connection string.)

  What are some things I can investigate to figure out what is happening here?

  Thanks

• Site Owners

  SSC Guru

  Points: 80325

  More actions

  December 3, 2020 at 2:10 pm

  #3816514

  Thanks for posting your issue and hopefully someone will answer soon.

  This is an automated bump to increase visibility of your question.