Forced plans on readable secondaries

Question

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:

• Plan Guides
• Query Store Forced Plan

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.

• Query Store and Availability Groups by Erin Stellato
• Query Data Store Forced Plan behavior on AlwaysOn Readable Secondary by Vikas Rana

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

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:

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

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:

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">

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 
WHERE 
    NOT EXISTS 
    (
        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" ...>

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">