Do you have a comment or suggestion to improve SQL Server? We’d love to hear it!

Enable Query Store for collection on a read-only replica in an Availability Group

Currently, Query Store can only be enabled for the read-write database in Availability Group. As many customers issue queries against the read-only replicas, capturing query and performance metrics for those queries would be beneficial for understanding the workload, troubleshooting performance issues, etc.

220 votes
Sign in
(thinking…)
Sign in with: oidc
Signed in as (Sign out)

We’ll send you updates on this idea

AdminMicrosoft SQL Server (Product Manager, Microsoft Azure) shared this idea  ·   ·  Flag idea as inappropriate…  ·  Admin →

11 comments

Sign in
(thinking…)
Sign in with: oidc
Signed in as (Sign out)
Submitting...
  • Daniel Tipser commented  ·   ·  Flag as inappropriate

    It would be nice to have different Query Store for Read-Write replica and Read-Only replica. Or to have one Query Store, but with the statistics that can be split between replicas.

  • anish commented  ·   ·  Flag as inappropriate

    This is a much needed feature which should be in place, if we want to truly utilize Routing rules and load balance very large systems using AG.

  • Eric Russell commented  ·   ·  Flag as inappropriate

    Question have been raised here about where to contain the QueryStore data when the feature is enabled on a read-only secondary database. One suggestion would be to redirect QueryStore data to another database, either locally or on a remote server. This could act as a consolidated repository for QueryStore data; something like a QueryStore DataMart.

  • Mirza Baig commented  ·   ·  Flag as inappropriate

    This will be a huge benefit in performance tuning the readable secondary. It will make the readable secondary in the AG more usable. The workload is different anyway between the primary and secondary so it is better to have query store capture data separately for readable secondary.

  • Durgaprasad Potnuru commented  ·   ·  Flag as inappropriate

    This will be invaluable for us too, and assist us in growing and directing more traffic to secondary replicas. Please give it priority.

  • Steve Strelzyk commented  ·   ·  Flag as inappropriate

    I agree with Alex Friedman. Our environment uses Read-Only replicas to direct reporting traffic. Couldn't the framework determine if the database is part of an availability group and direct the DML to QA using the ag listener name. That way data still rights to the same place and is replicated around everywhere?

  • Alex Friedman commented  ·   ·  Flag as inappropriate

    This will be invaluable for us, and assist us in growing and directing more traffic to secondary replicas. Please give it priority.

Feedback and Knowledge Base