Last week at the PASS Summit I presented a pre-con and general session on Query Store. I had several questions related to Query Store and Availability Groups, so I thought I’d pull them all together and answer them here.
Q: What happens to the Query Store data when a database is in an Availability Group?
A: You can enable Query Store for any user database, including one in an AG, and since that data is stored in internal tables in the database, it exists in the replica copies as well (just like other system tables and user tables).
Q: Can you enable Query Store for a read-only replica?
A: No. Because the replica is read-only, and Query Store inherently writes data TO the database, you cannot enable it to capture queries that are executed against that read-only copy. I did create a
Connect UserVoice item for this request. If this is of interest to you, please up-vote it: Enable Query Store for collection on a read-only replica in an Availability Group. The more votes this has, the better the possibility that Microsoft will implement it, so feel free to share with your friends and have them vote too!
*Update March 8, 2018: Connect has ended and all requests were ported to UserVoice…and your votes are still needed. Please vote when you have a chance!
Q: Can you force a plan for a query on a read-only replica if you force it first for that query on the primary?
A: No. With a read-only database this is not possible.
Q: Can you create a separate user database on the secondary, enable Query Store for it, and then issue queries against the read-only replica through that user database and have them captured in Query Store?
A: No. (But it’s a really creative idea.)
Have other questions related to Query Store and AGs? Let me know! Happy to answer them.