sp_Blitz - "Query Store Disabled" false positive on secondary replica

[petervandivier]

Version of the script

• 8.20
• 2024-05-22

What is the current behavior?

When Query Store has been enabled on a database in an AG, the DMV sys.database_query_store_options reports desired_state = 0 on the secondary replica until failover.

I noticed on a long-lived HA RDS instance that has not failed over since I enabled Query Store that the warning is popping a false positive from Line 6767

If the current behavior is a bug, please provide the steps to reproduce.

1. Create DB in an AG
2. Enable Query Store
3. Run sp_Blitz on a secondary replica
4. Observe the warning for check 160

What is the expected behavior?

Presumably this check could be ignored on a secondary replica where <some criteria> exists to determine that QS may be enabled on the primary but no sensible modification to the sp_Blitz check query comes to mind at this time.

Which versions of SQL Server and which OS are affected by this issue? Did this work in previous versions of our procedures?

Microsoft SQL Server 2019 (RTM-CU16-GDR) (KB5014353) - 15.0.4236.7 (X64) May 29 2022 15:55:47 Copyright (C) 2019 Microsoft Corporation Enterprise Edition: Core-based Licensing (64-bit) on Windows Server 2016 Datacenter 10.0 (Build 14393: ) (Hypervisor)

AFAICT this may be a server-scoped DMV not picking up a database-scoped change in an AG. This would not be a novel event by any means and the specific problem is almost certainly very niche.

Unsure if a quick fix may be readily apparent to other readers of this issue but wanted to report it here so I could stop worrying that I hadn't.

[BrentOzar]

Yeah, I don't think there's a way to work around that. I tend to think of database-level changes as things you wanna test on each replica - for example, if you set up replication publications on one replica, and you haven't failed it over to another replica yet to make sure replication works when another replica is the primary, then you're not done yet. :-D