Collect replica_group_id so a force-plan recommendation can be replica-scoped, not just replica-labelled #1914

Spun out of #1882, which made the force-plan recommendation disclose its replica scope. This is the part that would let it BE scoped rather than only describe how.

Where #1882 stopped, and why

#1882 established from MS Learn that plan forcing is genuinely per-replica: sp_query_store_force_plan takes a fourth @replica_group_id argument, is always executed on the primary, and "Using the @replica_group_id argument defaults to the primary replica" when omitted. So the recommendation now prefers the PRIMARY's regression (matching the scope the emitted statement already had) and, for a secondary-only regression, renders a disclosure naming the replica plus the sys.query_store_replicas lookup.

What it does NOT do is emit @replica_group_id, because the collector does not store one. QueryStoreCollector reads replica_role = qsr.replica_name — a role NAME ("Primary", "Secondary", "Geo Secondary", "Geo HA Secondary") — and never the id. There is no correct number to put in the statement, so #1882 describes the scoping step instead of emitting a call that would be a guess.

The three things to settle first

These are the reason this is not a mechanical follow-up. All three are unverified, and the docs cannot answer any of them:

1. Does SQL Server 2022's actual build of the proc accept @replica_group_id? The reference page is a ranged moniker (sql-server-2017 through ver17) and the argument block is not version-fenced, so the page cannot be read as an answer. Probe both boxes: SELECT p.name, p.parameter_id FROM sys.system_parameters AS p WHERE p.object_id = OBJECT_ID(N'sys.sp_query_store_force_plan') ORDER BY p.parameter_id;

2. Can a named-argument call skip @disable_optimized_plan_forcing? It sits third, ahead of @replica_group_id, and the same page warns that "Arguments for extended stored procedures must be entered in the specific order as described in the Syntax section. If the parameters are entered out of order, an error message occurs." Whether that tolerates a named skip decides whether the emitted statement needs a value for the third argument — and the docs do not state its default.

3. Is a persisted replica_group_id even safe to keep? sys.query_store_replicas accumulates a row per (replica, role) ACROSS FAILOVERS — "After a failover, it contains four rows: one row for each replica in both the primary and secondary roles" — and replica_name is documented NULL on Managed Instance. So an id captured at analysis time can silently re-point at a different replica by the time somebody runs the recommendation. It may need re-resolving at render time rather than storing, which is a different shape than adding a column.

Adjacent: the Apply path has no guard, because it currently cannot need one

ForcePlanTarget.ReplicaRole reaches the confirm modal and the executor as null in every reachable case today, so #1882 deliberately added no plumbing or gate for it: the executor is Dashboard-only, the Dashboard's own drill-down (SqlServerDrillDownCollector.Queries.cs) has no replica column at all, and Lite and Darling produce replica-aware targets but have no Apply path — they are copy-paste only, and their rendered text carries the disclosure. This is noted in DatabaseService.Remediation.cs.

If an Apply path ever consumes a replica-attributed target, that stops being true and a secondary-derived force could be applied to the primary through a modal that never mentions a replica. Whoever wires Apply into Lite or Darling, or gives the Dashboard's collector a replica column, owns adding the gate — RemediationApplyService → RemediationConfirmTarget → RemediationConfirmWindow is the path the field would have to travel.

Scope note

Zero user impact today on anything below SQL Server 2022, any standalone server, or any AG without Query Store for secondary replicas — replica_role is absent there and the recommendation renders exactly as it always has. Worth knowing that the feature is GA in SQL Server 2025 and is a limited preview behind trace flag 12606, explicitly not supported in production, on SQL Server 2022, so the population this would serve is small today and grows with 2025 adoption.