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][1]
* [Query Store Forced Plan][2]
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][3] by Erin Stellato
* [Query Data Store Forced Plan behavior on AlwaysOn Readable Secondary][4] 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.
[1]: https://docs.microsoft.com/en-us/sql/relational-databases/performance/plan-guides
[2]: https://docs.microsoft.com/en-us/sql/relational-databases/performance/monitoring-performance-by-using-the-query-store#force-a-plan-for-a-query-apply-forcing-policy
[3]: https://www.sqlskills.com/blogs/erin/query-store-and-availability-groups/
[4]: https://blogs.msdn.microsoft.com/vikas_rana/2017/10/13/query-data-store-forced-plan-behavior-on-alwayson-readable-secondary/
## 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][1]][1]
Now I'll click "Force Plan" on the right, and then refresh both views:
[![screenshot of query store UI showing both forced plans][2]][2]
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:
```lang-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][3]][3]
On the secondary (note the different server name), the plan was *not forced*. Here's the same plan XML snippet:
```lang-xml
<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][4]][4]
## 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:
```lang-xml
<StmtSimple StatementCompId="1" StatementEstRows="1000" ... StatementType="SELECT"
PlanGuideDB="..._UAT" PlanGuideName="plan-guide-test" ...>
```
[![screenshot of execution plan in SSMS showing plan guide attributes][5]][5]
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:
```lang-xml
<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][6]][6]
[1]: https://i.stack.imgur.com/5i0sj.png
[2]: https://i.stack.imgur.com/S6Jaz.png
[3]: https://i.stack.imgur.com/FA7Je.png
[4]: https://i.stack.imgur.com/3XLFP.png
[5]: https://i.stack.imgur.com/5aZU8.png
[6]: https://i.stack.imgur.com/WWJ1k.png
