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:

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

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

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:

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

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.