Forced plans on readable secondaries

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.

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:



And in the UI:

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



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:



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:



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.

StackExchange Database Administrators Q#246591, answer score: 23