unable to force a query store sql plan in Principal DB

Question

Hi,

I have a SQL 2017 STD edition mirroring setup and found a query has 2 plans in query store and I wanted to force the good plan which runs much faster. But when i force the query plan I get below error

Even though Always On is enabled at the instance level, we are not using it.

Why am I not allowed to force a plan on Principal DB and what is the workaround for this.

I tried to do it using tsql as well and get the same error

Thanks

Answer 1

Based on Microsoft's article "Query Store for secondary replicas":

... The Query Store exists on the primary replica and stores data for all replicas together. Currently, Query Store for secondary replicas is available with SQL Server 2022 (16.x) instances configured in availability groups.

Answer 2

Rather than pointing-and-clicking, what about running sp_query_store_force_plan directly from T-SQL? It seems that SSMS wants to run it both places.

Answer 3

Hi @udhayan d ,

The Query Store is a feature in SQL Server that captures a history of query execution plans and runtime statistics for each query, and it can help with query performance troubleshooting and optimization.

In a readable secondary replica configuration, the Query Store is not writable on the secondary replicas, which means that any changes to the Query Store on the primary replica will not be automatically replicated to the secondary replicas. However, the Query Store should still be available for read-only access on the secondary replicas.

To resolve the error message, you may want to check the following:

• Verify that the Query Store is enabled on the primary replica and that it is available for read-only access on the secondary replicas.
• Check the version and edition of SQL Server on the primary and secondary replicas to ensure they are compatible.
• Ensure that the Query Store is not disabled on the secondary replicas or that there are no replication issues that may be preventing the availability of the Query Store on the secondary replicas.

Best regards,

Seeya

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".