Applies to: SQL Server 2016 (13.x) and later Azure SQL Database Azure SQL Managed Instance
Enables forcing a particular plan for a particular query.
When a plan is forced for a particular query, every time SQL Server 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.
sp_query_store_force_plan [ @query_id = ] query_id , [ @plan_id = ] plan_id , [ @disable_optimized_plan_forcing = ] disable_optimized_plan_forcing [;]
[ @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.
[ @disable_optimized_plan_forcing = ] disable_optimized_plan_forcing
Indicates whether optimized plan forcing should be disabled.
disable_optimized_plan_forcing is a bit with a default of 0.
Return code values
0 (success) or 1 (failure)
The resulting execution plan forced by this feature will be the same or similar to the plan being forced. Because the resulting plan may not be identical to the plan specified by
sp_query_store_force_plan, the performance of the plans may vary. In rare cases, the performance difference may be significant and negative; in that case, the administrator must remove the forced plan.
Requires the ALTER permission on the database.
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 @query_id = 3, @plan_id = 3;
Learn more about related concepts in the following articles:
- 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