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

title	sp_query_store_force_plan (Transact-SQL) | Microsoft Docs
ms.custom
ms.date	03/29/2016
ms.prod	sql
ms.prod_service	database-engine, sql-database
ms.reviewer
ms.technology	system-objects
ms.topic	language-reference
f1_keywords	SYS.SP_QUERY_STORE_FORCE_PLAN SP_QUERY_STORE_FORCE_PLAN SYS.SP_QUERY_STORE_FORCE_PLAN_TSQL SP_QUERY_STORE_FORCE_PLAN_TSQL
dev_langs	TSQL
helpviewer_keywords	sys.sp_query_store_force_plan sp_query_store_force_plan
ms.assetid	0068f258-b998-4e4e-b47b-e375157c8213
author	stevestein
ms.author	sstein
monikerRange	=azuresqldb-current||>=sql-server-2016||=sqlallproducts-allversions||>=sql-server-linux-2017||=azuresqldb-mi-current

sp_query_store_force_plan (Transact-SQL)

[!INCLUDEtsql-appliesto-ss2016-asdb-xxxx-xxx-md]

Enables forcing a particular plan for a particular query.

When a plan is forced for a particular query, every time [!INCLUDEssNoVersion] 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.

Transact-SQL Syntax Conventions

Syntax

sp_query_store_force_plan [ @query_id = ] query_id , [ @plan_id = ] plan_id [;]  

Arguments

[ @query_id = ] query_id Is the id of the query. query_id is a bigint, with no default.

[ @plan_id = ] plan_id Is the id of the query plan to be forced. plan_id is a bigint, with no default.

Return Code Values

0 (success) or 1 (failure)

Remarks

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  
JOIN sys.query_store_query AS Qry  
    ON Pl.query_id = Qry.query_id  
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 3, 3;  

See Also

sp_query_store_remove_plan (Transct-SQL)
sp_query_store_remove_query (Transact-SQL)
sp_query_store_unforce_plan (Transact-SQL)
Query Store Catalog Views (Transact-SQL)
Monitoring Performance by using the Query Store
sp_query_store_reset_exec_stats (Transact-SQL)
sp_query_store_flush_db (Transact-SQL)
Best Practice with the Query Store