Different Query Store Settings for a Database in an Availability Group

Last week there was a question on #sqlhelp on Twitter about the status of Query Store for a database in an Availability Group. I’ve written about Query Store and Availability Groups before so if you’re not familiar with QS behavior in an AG, check out that post first. But this question was, I think, specific to the values that shows on a read-only replica and how there were different query store settings between a primary and secondary. Let’s set it up and take a look.

Environment
I have a multi-node cluster set up, and an AG (Avengers) running for the WideWorldImporters database across two nodes (CAP\ROGERS and BUCKY\BARNES). Right now, CAP is primary and BUCKY is secondary:

Avengers AG Status
Avengers AG Status

 

Query Store
On the primary, if you check the status of Query Store in the UI, we see the following:

Query Store configuration for WideWorldImporters on CAP
Query Store configuration for WideWorldImporters on CAP

 

If you check the status of Query Store from the secondary, the settings are the same:

Query Store configuration for WideWorldImporters on BUCKY
Query Store configuration for WideWorldImporters on BUCKY

 

Now we’ll make a change to the Query Store on the primary using TSQL:

USE [master];
GO
ALTER DATABASE [WideWorldImporters]
SET QUERY_STORE (MAX_STORAGE_SIZE_MB = 1024);
GO

And if we check the settings again, we see they’re updated on the primary:

Query Store configuration for WideWorldImporters after change on CAP
Query Store configuration for WideWorldImporters after change on CAP

 

But on the secondary, you’ll see that settings show the same values as before:

Query Store configuration for WideWorldImporters after change on BUCKY
Query Store configuration for WideWorldImporters after change on BUCKY

 

This is expected.  When the instance starts up and the database loads on a replica, the Query Store settings are pulled from sys.database_query_store_options, and these are cached in memory. Any change that is subsequently made to the configuration of Query Store on the primary is persisted to disk on the primary and then propagated over to the secondary and written to disk, just like anything else. However, on-disk changes to Query Store settings are only propagated to the memory cache when the secondary replica is restarted, or if there is a failover and the secondary becomes primary.

Conclusion

If you’re seeing a disparity between values for Query Store settings on a primary and any or all of your replicas, it is expected and you can be assured that the changes have been written to the secondary copies and committed.

3 thoughts on “Different Query Store Settings for a Database in an Availability Group

  1. Hi Erin,

    Thank you for the blog post. I think you meant to say — “But on the primary, you’ll see that settings show the same values as before:” — but on the secondary.

    Thanks,
    Denis

Leave a Reply

Your email address will not be published. Required fields are marked *

Other articles

A Fond Farewell

If you haven’t guessed from the title, I’m writing this post because I am leaving SQLskills. This Friday, January 14th, is my last day, and

Explore

Imagine feeling confident enough to handle whatever your database throws at you.

With training and consulting from SQLskills, you’ll be able to solve big problems, elevate your team’s capacity, and take control of your data career.