Edit

Share via

sys.query_store_plan_forcing_locations (Transact-SQL)

Applies to: SQL Server 2025 (17.x) Azure SQL Database

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.

Query Store for secondary replicas is supported starting in SQL Server 2025 (17.x) and later versions, and in Azure SQL Database. For complete platform support, see Query Store for secondary replicas.

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

Related content

  • Last updated on