sys.query_store_plan (Transact-SQL)sys.query_store_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 是Azure Synapse AnalyticsAzure Synapse AnalyticsyesAzure Synapse AnalyticsAzure Synapse Analytics适用于：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 是Azure Synapse AnalyticsAzure Synapse AnalyticsyesAzure Synapse AnalyticsAzure Synapse Analytics

包含有关与查询关联的每个执行计划的信息。Contains information about each execution plan associated with a query.

列名称Column name 数据类型Data type 说明Description
plan_idplan_id bigintbigint 主密钥。Primary key.
query_idquery_id bigintbigint 外键。Foreign key. 联接到 (transact-sql)sys.query_store_query 。Joins to sys.query_store_query (Transact-SQL).
plan_group_idplan_group_id bigintbigint 计划组的 ID。ID of the plan group. 游标查询通常需要多 (填充和提取) 计划。Cursor queries typically require multiple (populate and fetch) plans. 填充和提取一起编译的计划位于同一个组中。Populate and fetch plans that are compiled together are in the same group.

0表示计划不在组中。0 means plan is not in a group.
engine_versionengine_version nvarchar(32)nvarchar(32) 用于以 "主版本. 内部版本. 修订版本" 格式编译计划的引擎版本。Version of the engine used to compile the plan in 'major.minor.build.revision' format.
compatibility_levelcompatibility_level smallintsmallint 查询中引用的数据库的数据库兼容级别。Database compatibility level of the database referenced in the query.
query_plan_hashquery_plan_hash **二进制 (8) **binary(8) 单个计划的 MD5 哈希。MD5 hash of the individual plan.
query_planquery_plan nvarchar(max)nvarchar(max) 查询计划的显示计划 XML。Showplan XML for the query plan.
is_online_index_planis_online_index_plan bitbit 在联机索引生成过程中使用了 Plan。Plan was used during an online index build.
注意： Azure Synapse Analytics 将始终返回零 (0) 。Note: Azure Synapse Analytics will always return zero (0).
is_trivial_planis_trivial_plan bitbit 计划是查询优化器) 的阶段0中 (输出的普通计划。Plan is a trivial plan (output in stage 0 of query optimizer).
注意： Azure Synapse Analytics 将始终返回零 (0) 。Note: Azure Synapse Analytics will always return zero (0).
is_parallel_planis_parallel_plan bitbit 计划是并行的。Plan is parallel.
注意： Azure Synapse Analytics 将始终返回一个 (1) 。Note: Azure Synapse Analytics will always return one (1).
is_forced_planis_forced_plan bitbit 当用户执行存储过程 sys.sp_query_store_force_plan时，计划将标记为 "强制"。Plan is marked as forced when user executes stored procedure sys.sp_query_store_force_plan. 强制机制 并不保证 确切地将此计划用于 query_id引用的查询。Forcing mechanism does not guarantee that exactly this plan will be used for the query referenced by query_id. 计划强制再次编译查询，并通常为 plan_id所引用的计划生成完全相同或类似的计划。Plan forcing causes query to be compiled again and typically produces exactly the same or similar plan to the plan referenced by plan_id. 如果计划强制不成功，则 force_failure_count 会递增，并且 last_force_failure_reason 会按失败原因进行填充。If plan forcing does not succeed, force_failure_count is incremented and last_force_failure_reason is populated with the failure reason.
注意： Azure Synapse Analytics 将始终返回零 (0) 。Note: Azure Synapse Analytics will always return zero (0).
is_natively_compiledis_natively_compiled bitbit 计划包括本机编译的内存优化过程。Plan includes natively compiled memory optimized procedures. (0 = FALSE，1 = TRUE) 。(0 = FALSE, 1 = TRUE).
注意： Azure Synapse Analytics 将始终返回零 (0) 。Note: Azure Synapse Analytics will always return zero (0).
force_failure_countforce_failure_count bigintbigint 强制此计划失败的次数。Number of times that forcing this plan has failed. 仅当重新编译查询时，它才会递增 (不会在每次执行) 时递增。It can be incremented only when the query is recompiled (not on every execution). 每次将 is_plan_forced 从 FALSE 更改为 TRUE时，它都会重置为0。It is reset to 0 every time is_plan_forced is changed from FALSE to TRUE.
注意： Azure Synapse Analytics 将始终返回零 (0) 。Note: Azure Synapse Analytics will always return zero (0).
last_force_failure_reasonlast_force_failure_reason intint 计划强制失败的原因。Reason why plan forcing failed.

0：无故障，否则导致强制失败的错误的错误号0: no failure, otherwise error number of the error that caused the forcing to fail

8637： ONLINE_INDEX_BUILD8637: ONLINE_INDEX_BUILD

8683： INVALID_STARJOIN8683: INVALID_STARJOIN

8684： TIME_OUT8684: TIME_OUT

8689： NO_DB8689: NO_DB

8690： HINT_CONFLICT8690: HINT_CONFLICT

8691： SETOPT_CONFLICT8691: SETOPT_CONFLICT

8694： DQ_NO_FORCING_SUPPORTED8694: DQ_NO_FORCING_SUPPORTED

8698： NO_PLAN8698: NO_PLAN

8712： NO_INDEX8712: NO_INDEX

8713： VIEW_COMPILE_FAILED8713: VIEW_COMPILE_FAILED

<other value>： GENERAL_FAILURE<other value>: GENERAL_FAILURE
注意： Azure Synapse Analytics 将始终返回零 (0) 。Note: Azure Synapse Analytics will always return zero (0).
last_force_failure_reason_desclast_force_failure_reason_desc nvarchar(128)nvarchar(128) Last_force_failure_reason_desc 的文本说明。Textual description of last_force_failure_reason_desc.

ONLINE_INDEX_BUILD：查询在目标表具有正在联机生成的索引时尝试修改数据ONLINE_INDEX_BUILD: query tries to modify data while target table has an index that is being built online

INVALID_STARJOIN：计划包含无效的 StarJoin 规范INVALID_STARJOIN: plan contains invalid StarJoin specification

TIME_OUT：优化器在搜索由强制计划指定的计划时超出了允许的操作数TIME_OUT: Optimizer exceeded number of allowed operations while searching for plan specified by forced plan

NO_DB：在计划中指定的数据库不存在NO_DB: A database specified in the plan does not exist

HINT_CONFLICT：无法编译查询，因为计划与查询提示冲突HINT_CONFLICT: Query cannot be compiled because plan conflicts with a query hint

DQ_NO_FORCING_SUPPORTED：无法执行查询，因为计划与分布式查询或全文操作的使用冲突。DQ_NO_FORCING_SUPPORTED: Cannot execute query because plan conflicts with use of distributed query or full-text operations.

NO_PLAN：查询处理器无法生成查询计划，因为无法验证强制计划是否对查询有效NO_PLAN: Query processor could not produce query plan because forced plan could not be verified to be valid for the query

NO_INDEX：计划中指定的索引已不存在NO_INDEX: Index specified in plan no longer exists

VIEW_COMPILE_FAILED：由于在计划中引用的索引视图中存在问题，无法强制执行查询计划VIEW_COMPILE_FAILED: Could not force query plan because of a problem in an indexed view referenced in the plan

GENERAL_FAILURE：上述原因未涵盖 (常规强制错误) GENERAL_FAILURE: general forcing error (not covered with reasons above)
注意： Azure Synapse Analytics 将始终返回 NONE。Note: Azure Synapse Analytics will always return NONE.
count_compilescount_compiles bigintbigint 规划编译统计信息。Plan compilation statistics.
initial_compile_start_timeinitial_compile_start_time datetimeoffsetdatetimeoffset 规划编译统计信息。Plan compilation statistics.
last_compile_start_timelast_compile_start_time datetimeoffsetdatetimeoffset 规划编译统计信息。Plan compilation statistics.
last_execution_timelast_execution_time datetimeoffsetdatetimeoffset 上次执行时间是指查询/计划的最后结束时间。Last execution time refers to the last end time of the query/plan.
avg_compile_durationavg_compile_duration floatfloat 规划编译统计信息。Plan compilation statistics.
注意： Azure Synapse Analytics 将始终返回零 (0) 。Note: Azure Synapse Analytics will always return zero (0).
last_compile_durationlast_compile_duration bigintbigint 规划编译统计信息。Plan compilation statistics.
注意： Azure Synapse Analytics 将始终返回零 (0) 。Note: Azure Synapse Analytics will always return zero (0).
plan_forcing_typeplan_forcing_type intint 计划强制类型。Plan forcing type.

0：无0: NONE

1：手动1: MANUAL

2：自动2: AUTO
plan_forcing_type_descplan_forcing_type_desc nvarchar(60)nvarchar(60) Plan_forcing_type 的文本说明。Text description of plan_forcing_type.

无：无计划强制NONE: No plan forcing

手动：由用户强制执行的计划MANUAL: Plan forced by user

自动：由自动优化强制执行计划AUTO: Plan forced by automatic tuning

计划强制限制Plan forcing limitations

查询存储中具有一种可用于强制查询优化器使用特定执行计划的机制。Query Store has a mechanism to enforce Query Optimizer to use certain execution plan. 但是，有些限制可能会阻止计划强制执行。However, there are some limitations that can prevent a plan to be enforced.

首先，计划是否包含以下构造：First, if the plan contains following constructions:

  • INSERT BULK 语句。Insert bulk statement.
  • 对外部表的引用Reference to an external table
  • 分布式查询或全文操作Distributed query or full-text operations
  • 使用全局查询Use of Global queries
  • 动态或键集游标Dynamic or keyset cursors
  • 无效的星型联接规范Invalid star join specification

备注

Azure SQL Database 和 SQL Server 2019 支持计划强制用于静态和快进游标。Azure SQL Database and SQL Server 2019 support plan forcing for static and fast forward cursors.

其次，计划依赖的对象何时不再可用：Second, when objects that plan relies on, are no longer available:

  • 数据库（计划来自的数据库不再存在时）Database (if Database, where plan originated, does not exist anymore)
  • 索引（不再存在或已禁用）Index (no longer there or disabled)

最后，计划本身的问题：Finally, problems with the plan itself:

  • 用于查询不合法Not legal for query
  • 查询优化器超出了允许的操作数Query Optimizer exceeded number of allowed operations
  • 格式不正确的计划 XMLIncorrectly formed plan XML

权限Permissions

需要 VIEW DATABASE STATE 权限。Requires the VIEW DATABASE STATE permission.

另请参阅See Also

sys.database_query_store_options (Transact-sql) sys.database_query_store_options (Transact-SQL)
sys.query_context_settings (Transact-sql) sys.query_context_settings (Transact-SQL)
sys.query_store_query (Transact-sql) sys.query_store_query (Transact-SQL)
sys.query_store_query_text (Transact-sql) sys.query_store_query_text (Transact-SQL)
sys.query_store_runtime_stats (Transact-sql) sys.query_store_runtime_stats (Transact-SQL)
sys.query_store_wait_stats (Transact-SQL)sys.query_store_wait_stats (Transact-SQL)
sys.query_store_runtime_stats_interval (Transact-sql) sys.query_store_runtime_stats_interval (Transact-SQL)
相关视图、函数和过程 Monitoring Performance By Using the Query Store
目录视图 (Transact-SQL) Catalog Views (Transact-SQL)
查询存储存储过程 (Transact-SQL)Query Store Stored Procedures (Transact-SQL)