[BUG] Query store reported as being off on Secondary Replica

[GintarasDev]

Component

Desktop App (macOS)

Performance Studio Version

1.14.1

Operating System

macOS 26.4.1

Describe the Bug

When trying to connect to Azure SQL Server Secondary Replica, Query store not enabled (OFF) error appears.

Secondary replica seems to properly duplicate query store data from primary. The data is there, and using SSMS query store tools picks it up and displays everything properly.

Not really sure, maybe this checkbox being checked should solve the issue:
Read-only intent (for AG listeners and readable replicas - including Query Store on secondary replicas, but it seems to do nothing in my case and the issue is reproducible regardless of its state.

Steps to Reproduce

1. Create Azure SQL Server database.
2. Configure Secondary Replica for it.
3. Run some queries on the Primary.
4. Observe that query store on secondary contains information about queries that ran on primary.
5. Try opening query store using Secondary Replica through Performance Studio tool.

Expected Behavior

Query Store tool opens successfully and displays Primary database's query store data.

Actual Behavior

Query store not enabled (OFF) is displayed after login and the query store does not open.

Plan File

No response

Screenshots

No response

[erikdarlingdata]

Thanks for the detailed report, and for re-filing with the full template. I've reproduced this and tracked down the cause.

Root cause. Performance Studio gates Query Store access on sys.database_query_store_options.actual_state_desc and only accepts a READ* value (READ_ONLY / READ_WRITE). On a readable secondary the database is read-only, so the local capture engine can't run and the state comes back as something else — which the app wrongly treated as "not enabled." As you said, the primary's captured data is replicated into the database's internal tables and is fully readable through the sys.query_store_* views (which is why SSMS shows it), so the tool was refusing a scenario that actually works. The "Read-only intent" checkbox only sets ApplicationIntent=ReadOnly for routing and has no effect on that state check — which lines up with your "does nothing / reproducible regardless" note.

One thing I'd like to confirm from your environment. When I reproduce this on a current Azure SQL Database active geo-replica, the secondary reports actual_state_desc = READ_CAPTURE_SECONDARY (the "Query Store for secondary replicas" feature, on by default in that config), and that value actually does pass the old check. You're seeing OFF, which is the older / feature-off behavior — so your secondary is set up differently from what I stood up. To make sure the fix matches your exact case, could you run this against the secondary and paste the result:

SELECT actual_state_desc, DATABASEPROPERTYEX(DB_NAME(),'Updateability') AS updateability, (SELECT COUNT(*) FROM sys.query_store_query) AS qcount FROM sys.database_query_store_options;

I'm expecting OFF / READ_ONLY / >0. It would also help to know what kind of secondary it is — active geo-replication to a separate logical server, read scale-out (ApplicationIntent=ReadOnly on Business Critical/Hyperscale), a failover-group listener, Managed Instance, or SQL Server on a VM with an availability group. That explains why you get OFF where I got READ_CAPTURE_SECONDARY.

Fix. The readability check no longer trusts the state string alone: a read-only database that already holds Query Store data is treated as readable, so OFF, READ_ONLY, and READ_CAPTURE_SECONDARY on a read-only secondary all open correctly. A genuinely-off writable primary is still reported as off (with a corrected message that no longer tells you to run ALTER DATABASE ... SET QUERY_STORE = ON on a database you can't write to). The qcount from the query above is exactly the signal the new check uses to confirm there's data to read.

Thanks again — good catch.

[GintarasDev]

Thanks for a quick response 🏅

I ran the query, here is the result: OFF READ_ONLY 438195 - it sounds like the fix you described should cover my case.

It would also help to know what kind of secondary it is

I'm using Hyperscale Named Replica

[erikdarlingdata]

Perfect - OFF / READ_ONLY / 438195 is exactly the case the fix targets, so your Hyperscale named replica will open correctly once this ships.

That also explains the OFF vs READ_CAPTURE_SECONDARY difference: a named replica doesn't run the secondary-capture feature, so its local Query Store state reads OFF, but it reads the same shared page-server storage as the primary, so the primary's captured data is still served through sys.query_store_* - which is why SSMS shows it and your qcount is ~438k. The new check keys off exactly that (read-only database + data present) rather than the misleading OFF state.

I'll follow up here when the build with the fix is released. Thanks for the fast turnaround.