sp_query_store_unforce_plan (Transact-SQL)
Applies to: SQL Server 2016 (13.x) and later Azure SQL Database Azure SQL Managed Instance
Enables unforcing a previously forced plan for a particular query in the Query Store.
Transact-SQL syntax conventions
Syntax
sp_query_store_unforce_plan
[ @query_id = ] query_id ,
[ @plan_id = ] plan_id ,
[ @force_plan_scope = ] 'replica_group_id'
[ ; ]
Arguments
[ @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 that will no longer be enforced. @plan_id is bigint, with no default.
[ @force_plan_scope = ] 'replica_group_id'
You can force and unforce 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 secondary replica. The optional @force_plan_scope argument defaults only to the local replica, but you can optionally specify a replica_group_id referencing sys.query_store_plan_forcing_locations.
Return code values
0
(success) or 1
(failure).
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 unforce, use the following example to unforce the plan.
EXEC sp_query_store_unforce_plan 3, 3;
Related content
- sys.query_store_replicas (Transact-SQL)
- sys.query_store_plan_forcing_locations (Transact-SQL)
- sp_query_store_force_plan (Transact-SQL)
- sp_query_store_remove_plan (Transact-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
- Best Practice with the Query Store
Σχόλια
https://aka.ms/ContentUserFeedback.
Σύντομα διαθέσιμα: Καθ' όλη τη διάρκεια του 2024 θα καταργήσουμε σταδιακά τα ζητήματα GitHub ως μηχανισμό ανάδρασης για το περιεχόμενο και θα το αντικαταστήσουμε με ένα νέο σύστημα ανάδρασης. Για περισσότερες πληροφορίες, ανατρέξτε στο θέμα:Υποβολή και προβολή σχολίων για