sys.query_store_plan (Transact-SQL)sys.query_store_plan (Transact-SQL)
适用于:Applies to: SQL Server 2016 (13.x)SQL Server 2016 (13.x)
SQL Server 2016 (13.x)SQL Server 2016 (13.x)
Azure SQL 数据库Azure SQL Database
Azure SQL 数据库Azure SQL Database
Azure SQL 托管实例Azure SQL Managed Instance
Azure SQL 托管实例Azure SQL Managed Instance
Azure Synapse AnalyticsAzure Synapse Analytics
Azure Synapse AnalyticsAzure Synapse Analytics
SQL Server 2016 (13.x)SQL Server 2016 (13.x)
SQL Server 2016 (13.x)SQL Server 2016 (13.x)
Azure SQL 数据库Azure SQL Database
Azure SQL 数据库Azure SQL Database
Azure SQL 托管实例Azure SQL Managed Instance
Azure SQL 托管实例Azure SQL Managed Instance
Azure Synapse AnalyticsAzure Synapse Analytics
Azure 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)