Skip to content

Latest commit

 

History

History
125 lines (93 loc) · 5.6 KB

sp-query-store-force-plan-transact-sql.md

File metadata and controls

125 lines (93 loc) · 5.6 KB
title description author ms.author ms.reviewer ms.date ms.service ms.subservice ms.topic f1_keywords helpviewer_keywords dev_langs monikerRange
sp_query_store_force_plan (Transact-SQL)
Enables forcing a particular plan for a particular query in the Query Store.
markingmyname
maghan
randolphwest
03/19/2025
sql
system-objects
reference
SYS.SP_QUERY_STORE_FORCE_PLAN
SP_QUERY_STORE_FORCE_PLAN
SYS.SP_QUERY_STORE_FORCE_PLAN_TSQL
SP_QUERY_STORE_FORCE_PLAN_TSQL
sys.sp_query_store_force_plan
sp_query_store_force_plan
TSQL
=azuresqldb-current || >=sql-server-2016 || >=sql-server-linux-2017 || =azuresqldb-mi-current

sp_query_store_force_plan (Transact-SQL)

[!INCLUDE sqlserver2016-asdb-asdbmi]

Enables forcing a particular plan for a particular query in the Query Store.

When a plan is forced for a particular query, every time [!INCLUDE ssNoVersion] encounters the query, it tries to force the plan in the Query Optimizer. If plan forcing fails, an Extended Event is fired and the Query Optimizer is instructed to optimize in the normal way.

:::image type="icon" source="../../includes/media/topic-link-icon.svg" border="false"::: Transact-SQL syntax conventions

Syntax

sp_query_store_force_plan
    [ @query_id = ] query_id ,
    [ @plan_id = ] plan_id ,
    [ @disable_optimized_plan_forcing = ] disable_optimized_plan_forcing ,
    [ @force_plan_scope = ] 'replica_group_id'
[ ; ]

Arguments

[!INCLUDE extended-stored-procedures]

[ @query_id = ] query_id

The ID of the query. @query_id is bigint, with no default.

[ @plan_id = ] plan_id

The ID of the query plan to be forced. @plan_id is bigint, with no default.

[ @disable_optimized_plan_forcing = ] disable_optimized_plan_forcing

Indicates whether optimized plan forcing should be disabled. @disable_optimized_plan_forcing is bit with a default of 0.

[ @force_plan_scope = ] 'replica_group_id'

You can force plans on a secondary replica when Query Store for secondary replicas is enabled. Execute sp_query_store_force_plan and sp_query_store_unforce_plan on the primary replica. Using the @force_plan_scope argument defaults to the local replica where the command is being executed, but you can specify a replica_group_id referencing the sys.query_store_plan_forcing_locations system catalog view.

Return code values

0 (success) or 1 (failure).

Remarks

The resulting execution plan forced by this feature is the same or similar to the plan being forced. Because the resulting plan might not be identical to the plan specified by sys.sp_query_store_force_plan, the performance of the plans might vary. In rare cases, the performance difference might be significant and negative; in that case, the administrator must remove the forced plan.

Review forced plans on secondary replicas with sys.query_store_plan_forcing_locations.

Permissions

Requires the ALTER permission on the database.

Examples

The following example returns information about the queries in the Query Store.

SELECT txt.query_text_id,
    txt.query_sql_text,
    pl.plan_id,
    qry.*
FROM sys.query_store_plan AS pl
INNER JOIN sys.query_store_query AS qry
    ON pl.query_id = qry.query_id
INNER JOIN sys.query_store_query_text AS txt
    ON qry.query_text_id = txt.query_text_id;

After you identify the query_id and plan_id that you want to force, use the following example to force the query to use a plan.

EXEC sp_query_store_force_plan
    @query_id = 3,
    @plan_id = 3;

Use sys.query_store_plan_forcing_locations, joined with sys.query_store_replicas, to retrieve Query Store for 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