If a plan is forced on the primary in an Availability Group, is it applied to queries run on a secondary?

I'm looking for answers that cover both possibilities for plan forcing:

I have read the following that suggest QS forced plans do not carry over, but cannot find anything authoritative in the documentation, or anything about plan guides.

Conclusive evidence of forcing would be the presence of Use Plan or PlanGuideName and PlanGuideDB properties in the secondary's execution plan.

1 Answer 1


Query Store plan forcing does NOT affect queries on the secondary

Using Query Store to force a plan on the primary certainly looks like it forces the plan on the secondary.

I tried running a query on a non-prod server, and then flushing the query store with sp_query_store_flush_db (which was required to get the data to sync across to the secondary). Here's the secondary on the left (note the circled warning about being "read only"), and the primary on the right:

screenshot of query store UI

Now I'll click "Force Plan" on the right, and then refresh both views:

screenshot of query store UI showing both forced plans

So the "forcing" at least carried over in the underlying Query Store tables. This makes sense, given that the articles quoted in the OP make the point that query forcing should remain in-place after a failover:

Question: Will QDS retain FORCED Plan information when Database failover from Primary replica to secondary Replica?

Answer: Yes, QDS store Forced Plan information in sys.query_store_plan table, so in case of failover you will continue to see same behavior on new Primary.

But does the forcing behavior actually take place? I'll now run the same query on both servers. On the primary, as expected, the "UsePlan" attribute is there in the plan XML:

<QueryPlan DegreeOfParallelism="1" MemoryGrant="11096" CachedPlanSize="288" CompileTime="82"
           CompileCPU="78" CompileMemory="2104" UsePlan="true">

And in the UI:

screenshot of execution plan in SSMS showing the "use plan" attribute

On the secondary (note the different server name), the plan was not forced. Here's the same plan XML snippet:

<QueryPlan DegreeOfParallelism="1" MemoryGrant="11096" CachedPlanSize="288" CompileTime="32" 
           CompileCPU="28" CompileMemory="1656">

screenshot of execution plan in SSMS showing the no "use plan" attribute

Plan Guides do NOT affect queries on the secondary

I created a plan guide on the primary using this code (table names changed to protect the innocent):

EXEC sp_create_plan_guide 
    @name = 'plan-guide-test',
    @stmt = N'SELECT TOP (1000) * 
FROM dbo.TableName t 
        SELECT NULL 
        FROM dbo.OtherTable o 
        WHERE t.Id = o.TableName
    @type = N'SQL',
    @module_or_batch = NULL,
    @hints = N'OPTION (MAXDOP 1)';

The plan guide was, of course, effective on the primary, as evidenced by the execution plan:

<StmtSimple StatementCompId="1" StatementEstRows="1000" ... StatementType="SELECT" 
            PlanGuideDB="..._UAT" PlanGuideName="plan-guide-test" ...>

screenshot of execution plan in SSMS showing plan guide attributes

I did confirm at this point that the plan guide was replicated to the secondary.

Running the same query on the secondary, the execution plan is missing all the signs of being forced by a plan guide:

<StmtSimple StatementCompId="1" StatementEstRows="1000" ... StatementType="SELECT" 
            QueryHash="0xECF8A24F126EE77A" QueryPlanHash="0x0E93CF7FEAC1B6EA" 
            RetrievedFromCache="true" SecurityPolicyApplied="false">

screenshot of execution plan in XML with missing plan guide attributes

SQL Server 2022

Query store plan forcing may optionally be applied to the secondary. See sys.query_store_plan_forcing_locations and related documentation for details. This feature is currently in preview and not supported for production deployments.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.