Skip to content

Latest commit

 

History

History
61 lines (51 loc) · 3.53 KB

sys-query-store-plan-forcing-locations-transact-sql.md

File metadata and controls

61 lines (51 loc) · 3.53 KB
title description author ms.author ms.date ms.service ms.subservice ms.topic f1_keywords helpviewer_keywords dev_langs monikerRange
sys.query_store_plan_forcing_locations (Transact-SQL)
The sys.query_store_plan_forcing_locations system view contains information about where Query Store plans have been forced on secondary replicas.
MikeRayMSFT
mikeray
10/14/2022
sql
system-objects
reference
SYS.query_store_plan_forcing_locations_TSQL
query_store_plan_forcing_locations_TSQL
SYS.query_store_plan_forcing_locations
query_store_plan_forcing_locations
query_store_plan_forcing_locations catalog view
sys.query_store_plan_forcing_locations catalog view
TSQL
>=sql-server-ver16||>=sql-server-linux-ver16||=azuresqldb-mi-current

sys.query_store_plan_forcing_locations (Transact-SQL)

[!INCLUDE sqlserver2022-asmi]

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