Edit

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 are forced by using sp_query_store_force_plan. Use this information to determine which queries have plans forced on a database's read/write replica (primary) and one or more read-only 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
timestamp datetime UTC date and time when the plan forcing operation was applied.
plan_forcing_type int Type of plan forcing.

0 = NONE
1 = MANUAL
2 = AUTO
plan_forcing_type_desc nvarchar(60) Text description of plan_forcing_type.

NONE: No plan forcing
MANUAL: Plan forced by a user
AUTO: Plan forced by automatic tuning

Permissions

Requires the VIEW DATABASE STATE permission.

Permissions for SQL Server 2022 and later

Requires the VIEW DATABASE PERFORMANCE STATE permission on the database.

Example

Use sys.query_store_plan_forcing_locations, joined with sys.query_store_replicas, to retrieve the top 20 Query Store plans that have been forced.

SELECT TOP (20)
    pfl.query_id,
    pfl.plan_id,
    CASE qsr.replica_group_id
        WHEN 1 THEN 'PRIMARY'
        WHEN 2 THEN 'SECONDARY'
        WHEN 3 THEN 'GEO SECONDARY'
        WHEN 4 THEN 'GEO HA SECONDARY'
        ELSE CONCAT('REPLICA_', qsr.replica_group_id)
    END AS replica_type,
    pfl.[timestamp],
    pfl.plan_forcing_type_desc
FROM sys.query_store_plan_forcing_locations AS pfl
INNER JOIN sys.query_store_replicas AS qsr
    ON qsr.replica_group_id = pfl.replica_group_id
ORDER BY pfl.[timestamp] DESC;

Related content

  • Last updated on