Edit

sys.query_store_plan_forcing_locations (Transact-SQL)

  • Article

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';

Next steps