sys.query_store_plan_forcing_locations (Transact-SQL)
Applies to: 
SQL Server 2022 (16.x)
Azure SQL Managed Instance
Contains information about Query Store plans that have been forced on secondary replicas using sp_query_store_force_plan, when Query Store for secondary replicas is enabled. You can use this information to determine what queries have plans forced on different replica sets.
| Column name | Data type | Description |
|---|---|---|
| plan_forcing_location_id | bigint | System-assigned ID for this plan forcing location. |
| query_id | bigint | References query_id in sys.query_store_query |
| plan_id | bigint | References plan_id in sys.query_store_plan |
| replica_group_id | bigint | From the parameter force_plan_scope in sp_query_store_force_plan (Transact-SQL). References replica_group_id in sys.query_store_replicas |
Permissions
Requires the VIEW DATABASE STATE permission.
Example
Use sys.query_store_plan_forcing_locations, joined with sys.query_store_replicas, to retrieve Query Store plans forced on all secondary replicas.
SELECT query_plan
FROM sys.query_store_plan AS qsp
INNER JOIN sys.query_store_plan_forcing_locations AS pfl
ON pfl.query_id = qsp.query_id
INNER JOIN sys.query_store_replicas AS qsr
ON qsr.replica_group_id = qsp.replica_group_id
WHERE qsr.replica_name = 'yourSecondaryReplicaName';