sp_query_store_force_plan (Transact-sql) sp_query_store_force_plan (Transact-SQL)

适用于:Applies to: 是SQL Server 2016 (13.x)SQL Server 2016 (13.x)yesSQL Server 2016 (13.x)SQL Server 2016 (13.x) 及更高版本 是Azure SQL 数据库Azure SQL DatabaseYesAzure SQL 数据库Azure SQL Database 是Azure SQL 托管实例Azure SQL Managed InstanceYesAzure SQL 托管实例Azure SQL Managed Instance适用于:Applies to: 是SQL Server 2016 (13.x)SQL Server 2016 (13.x)yesSQL Server 2016 (13.x)SQL Server 2016 (13.x) and later 是Azure SQL 数据库Azure SQL DatabaseYesAzure SQL 数据库Azure SQL Database 是Azure SQL 托管实例Azure SQL Managed InstanceYesAzure SQL 托管实例Azure SQL Managed Instance

为特定查询启用强制特定计划。Enables forcing a particular plan for a particular query.

当对特定查询强制执行某个计划时,每次 SQL ServerSQL Server 遇到该查询时,它都会尝试在查询优化器中强制执行该计划。When a plan is forced for a particular query, every time SQL ServerSQL 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.

主题链接图标 Transact-SQL 语法约定Topic link icon Transact-SQL Syntax Conventions

语法Syntax

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

参数Arguments

[ @query_id = ] query_id 查询的 id。[ @query_id = ] query_id Is the id of the query. query_idbigint,无默认值。query_id is a bigint, with no default.

[ @plan_id = ] plan_id 要强制执行的查询计划的 id。[ @plan_id = ] plan_id Is the id of the query plan to be forced. plan_idbigint,无默认值。plan_id is a bigint, with no default.

返回代码值Return Code Values

0(成功)或 1(失败)0 (success) or 1 (failure)

备注Remarks

权限Permissions

要求对数据库具有 ALTER 权限。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 ;  

确定要强制执行的 query_id 和 plan_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_plan (Transct-SQL)
sp_query_store_remove_query (Transact-sql) sp_query_store_remove_query (Transact-SQL)
sp_query_store_unforce_plan (Transact-sql) sp_query_store_unforce_plan (Transact-SQL)
查询存储目录视图 (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_reset_exec_stats (Transact-SQL)
sp_query_store_flush_db (Transact-sql) sp_query_store_flush_db (Transact-SQL)
Query Store 最佳实践Best Practice with the Query Store