sp_query_store_unforce_plan (Transact-SQL)

THIS TOPIC APPLIES TO: yesSQL Server (starting with 2016)yesAzure SQL DatabasenoAzure SQL Data Warehouse noParallel Data Warehouse

Enables unforcing a particular plan for a particular query.

Topic link icon Transact-SQL Syntax Conventions

Syntax


sp_query_store_unforce_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 that will no longer be enforced. plan_id is a bigint, with no default.

Return Code Values

0 (success) or 1 (failure)

Remarks

Permissions

Requires the EXECUTE permission on the database, and INSERT, UPDATE, and DELETE permission on the query store catalog views.

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 unforce, use the following example to unforce the plan.

EXEC sp_query_store_unforce_plan 3, 3;  

See Also

sp_query_store_force_plan (Transact-SQL)
sp_query_store_remove_plan (Transct-SQL)
sp_query_store_remove_query (Transact-SQL)
sp_query_store_reset_exec_stats (Transact-SQL)
sp_query_store_flush_db (Transact-SQL)
Query Store Catalog Views (Transact-SQL)
Monitoring Performance By Using the Query Store