Force-plan recommendation is replica-blind now that plan regression splits by replica_role #1882

Spun out of #1850, which made the analysis-layer Query Store dedups key on replica_role and carry it through the drill-down row shape. That change is correct on its own terms, and it surfaces a question the remediation layer has never had to answer.

What #1850 changed

On a SQL Server 2022+ AG with Query Store for secondary replicas enabled, one query can now legitimately appear in the regressed_queries drill-down TWICE — once as PRIMARY, once as SECONDARY — with its own best plan on each, because the two replicas run genuinely different workloads against the same shared Query Store.

The mechanical half, already fixed in #1850

FactRemediation.ExtractPlanRegressionTargets reads a fixed set of keys and does not know about replica_role. Two rows for one query would have produced two ForcePlanTargets naming the SAME (database, query_id) with DIFFERENT best_plan_id, which renders as two sp_query_store_force_plan calls for one query — mutually exclusive instructions where whichever the operator runs last silently wins. #1850 dedups on (database, query_id), keeping the first, which the SQL orders regression_factor DESC so it is the worst. That stops the contradiction and nothing more.

What is left, and why it is not mechanical

1. Should a regression observed only on a read-only SECONDARY recommend forcing a plan at all? Forcing is done on the primary. A recommendation driven by read-only reporting workload changes the plan the primary's write workload gets.
2. When both replicas regressed, is "the worse regression factor" the right tiebreak? Today's dedup keeps it because it is consistent with the existing cap-of-5-worst behavior, not because anyone decided a secondary's 12x should outrank a primary's 3x.
3. Should ForcePlanTarget and the rendered recommendation NAME the replica? It is a positional record and the renderer is pinned by a render-stability golden test, so adding the field is a deliberate change rather than an additive one. The drill-down and the MCP payload already carry replica_role (AnalysisNotificationService.FlattenInto enumerates every property, so it renders in email/webhook/toast automatically) — it is only the typed remediation path that drops it.

Anyone picking this up should confirm the current SQL Server 2022+ semantics for plan forcing under Query Store for secondary replicas against MS Learn first, rather than reasoning from the row shape — whether forcing is per-query or can be scoped per replica is the fact the whole decision turns on, and #1850 deliberately did not assume an answer.

Impact bound

Zero on any standalone or non-AG server, and zero below SQL Server 2022: replica_role is NULL everywhere else, so the drill-down produces one row per query exactly as before and the dedup is a no-op.