sys.dm_exec_plan_attributes (Transact-SQL)sys.dm_exec_plan_attributes (Transact-SQL)

适用对象: 是SQL Server 否Azure SQL 数据库 否Azure Synapse Analytics (SQL DW) 否并行数据仓库 APPLIES TO: yesSQL Server noAzure SQL Database noAzure Synapse Analytics (SQL DW) noParallel Data Warehouse

针对计划句柄所指定计划的每个计划属性返回一行。Returns one row per plan attribute for the plan specified by the plan handle. 可以使用此表值函数获取有关特定计划的详细信息,例如计划的缓存键值或当前同步执行次数。You can use this table-valued function to get details about a particular plan, such as the cache key values or the number of current simultaneous executions of the plan.

备注

通过返回的信息的一些此函数将映射到sys.syscacheobjects向后兼容性视图。Some of the information returned through this function maps to the sys.syscacheobjects backward compatibility view.

语法Syntax

sys.dm_exec_plan_attributes ( plan_handle )  

参数Arguments

plan_handleplan_handle
用于唯一标识已执行并且其计划驻留在计划缓存中的批处理的查询计划。Uniquely identifies a query plan for a batch that has executed and whose plan resides in the plan cache. plan_handlevarbinary(64)plan_handle is varbinary(64). 可以从获取的计划句柄sys.dm_exec_cached_plans动态管理视图。The plan handle can be obtained from the sys.dm_exec_cached_plans dynamic management view.

返回的表Table Returned

列名Column name 数据类型Data type 描述Description
属性attribute varchar(128)varchar(128) 与此计划关联的属性的名称。Name of the attribute associated with this plan. 立即下此表列出了可能的属性、 其数据类型和及其说明。The table immediately below this one lists the possible attributes, their data types, and their descriptions.
valuevalue sql_variantsql_variant 与此计划关联的属性的值。Value of the attribute that is associated with this plan.
is_cache_keyis_cache_key bitbit 指示此属性是否用作计划的缓存查找密钥的一部分。Indicates whether the attribute is used as part of the cache lookup key for the plan.

从上表中,特性可以具有以下值:From the above table, attribute can have the following values:

特性Attribute 数据类型Data type 描述Description
set_optionsset_options intint 指示编译计划所使用的选项值。Indicates the option values that the plan was compiled with.
objectidobjectid intint 用于在缓存中查找对象的主键之一。One of the main keys used for looking up an object in the cache. 这是 ID 中存储的对象sys.objects的数据库对象 (过程、 视图、 触发器等)。This is the object ID stored in sys.objects for database objects (procedures, views, triggers, and so on). 对于类型为“即席”或“已准备好”的计划,它是批处理文本的内部哈希。For plans of type "Adhoc" or "Prepared", it is an internal hash of the batch text.
dbiddbid intint 是包含计划引用的实体的数据库 ID。Is the ID of the database containing the entity the plan refers to.

对于即席计划或已准备好的计划,它是执行批处理的数据库 ID。For ad hoc or prepared plans, it is the database ID from which the batch is executed.
dbid_executedbid_execute intint 为系统对象存储在资源数据库,请从其执行缓存的计划的数据库 ID。For system objects stored in the Resource database, the database ID from which the cached plan is executed. 对于所有其他情况,均为 0。For all other cases, it is 0.
user_iduser_id intint 值 -2 指示已提交的批处理不依赖于隐式名称解析并可在不同的用户间共享。Value of -2 indicates that the batch submitted does not depend on implicit name resolution and can be shared among different users. 这是首选方法。This is the preferred method. 任何其他值表示数据库中提交查询的用户的用户 ID。Any other value represents the user ID of the user submitting the query in the database.
language_idlanguage_id smallintsmallint 创建缓存对象的连接的语言 ID。ID of the language of the connection that created the cache object. 有关详细信息,请参阅sys.syslanguages (TRANSACT-SQL)For more information, see sys.syslanguages (Transact-SQL).
date_formatdate_format smallintsmallint 创建缓存对象的连接的日期格式。Date format of the connection that created the cache object. 有关详细信息,请参阅 SET DATEFORMAT (Transact-SQL).For more information, see SET DATEFORMAT (Transact-SQL).
date_firstdate_first tinyinttinyint 第一个日期值。Date first value. 有关详细信息,请参阅 SET DATEFIRST (Transact-SQL).For more information, see SET DATEFIRST (Transact-SQL).
statusstatus intint 缓存查找密钥中的内部状态位。Internal status bits that are part of the cache lookup key.
required_cursor_optionsrequired_cursor_options intint 用户指定的游标选项,例如游标类型。Cursor options specified by the user such as the cursor type.
acceptable_cursor_optionsacceptable_cursor_options intint 为了支持语句的执行,SQL ServerSQL Server 可以隐式转换采用的游标选项。Cursor options that SQL ServerSQL Server may implicitly convert to in order to support the execution of the statement. 例如,用户可能指定一个动态游标,但允许查询优化器将此游标类型转换为静态游标。For example, the user may specify a dynamic cursor, but the query optimizer is permitted to convert this cursor type to a static cursor.
inuse_exec_contextinuse_exec_context intint 使用查询计划并且当前正在执行的批处理的数目。Number of currently executing batches that are using the query plan.
free_exec_contextfree_exec_context intint 当前未使用的查询计划的缓存执行上下文数目。Number of cached execution contexts for the query plan that are not being currently used.
hits_exec_contexthits_exec_context intint 从计划缓存获取执行上下文并重用从而节省 SQL 语句编译开销的次数。Number of times the execution context was obtained from the plan cache and reused, saving the overhead of recompiling the SQL statement. 该值是目前所有批处理执行的聚合。The value is an aggregate for all batch executions so far.
misses_exec_contextmisses_exec_context intint 无法在计划缓存中找到执行上下文而导致为批处理执行创建新的执行上下文的次数。Number of times that an execution context could not be found in the plan cache, resulting in the creation of a new execution context for the batch execution.
removed_exec_contextremoved_exec_context intint 由于缓存计划的内存压力而删除的执行上下文数目。Number of execution contexts that have been removed because of memory pressure on the cached plan.
inuse_cursorsinuse_cursors intint 包含一个或多个使用缓存计划的游标的当前正在执行的批数。Number of currently executing batches containing one or more cursors that are using the cached plan.
free_cursorsfree_cursors intint 缓存计划的空闲或可用游标数。Number of idle or free cursors for the cached plan.
hits_cursorshits_cursors intint 从缓存计划获得不活动游标并重用的次数。Number of times that an inactive cursor was obtained from the cached plan and reused. 该值是目前所有批处理执行的聚合。The value is an aggregate for all batch executions so far.
misses_cursorsmisses_cursors intint 无法在缓存中找到不活动游标的次数。Number of times that an inactive cursor could not be found in the cache.
removed_cursorsremoved_cursors intint 由于缓存计划的内存压力而删除的游标数。Number of cursors that have been removed because of memory pressure on the cached plan.
sql_handlesql_handle varbinary(64)varbinary(64) 批处理的 SQL 句柄。The SQL handle for the batch.
merge_action_typemerge_action_type smallintsmallint 用作 MERGE 语句结果的触发器执行计划的类型。The type of trigger execution plan used as the result of a MERGE statement.

0 表示非触发器计划,或者不会作为 MERGE 语句结果来执行的触发器计划,或者作为仅指定 DELETE 操作的 MERGE 语句结果执行的触发器计划。0 indicates a non-trigger plan, a trigger plan that does not execute as the result of a MERGE statement, or a trigger plan that executes as the result of a MERGE statement that only specifies a DELETE action.

1 表示作为 MERGE 语句结果运行的 INSERT 触发器计划。1 indicates an INSERT trigger plan that runs as the result of a MERGE statement.

2 表示作为 MERGE 语句结果运行的 UPDATE 触发器计划。2 indicates an UPDATE trigger plan that runs as the result of a MERGE statement.

3 表示一个作为包含对应的 INSERT 或 UPDATE 操作的 MERGE 语句结果运行的 DELETE 触发器计划。3 indicates a DELETE trigger plan that runs as the result of a MERGE statement containing a corresponding INSERT or UPDATE action.

对于由级联操作运行的嵌套触发器,此值是导致级联的 MERGE 语句的操作。For nested triggers run by cascading actions, this value is the action of the MERGE statement that caused the cascade.

权限Permissions

SQL ServerSQL Server,需要VIEW SERVER STATE权限。On SQL ServerSQL Server, requires VIEW SERVER STATE permission.
SQL 数据库SQL Database高级层,需要VIEW DATABASE STATE数据库中的权限。On SQL 数据库SQL Database Premium Tiers, requires the VIEW DATABASE STATE permission in the database. SQL 数据库SQL Database标准版和基本层,需要服务器管理员Azure Active Directory 管理员帐户。On SQL 数据库SQL Database Standard and Basic Tiers, requires the Server admin or an Azure Active Directory admin account.

备注Remarks

Set 选项Set Options

相同编译计划的副本可能只是中的值不同set_options列。Copies of the same compiled plan might differ only by the value in the set_options column. 这说明不同的连接为相同的查询使用不同的 SET 选项集。This indicates that different connections are using different sets of SET options for the same query. 通常不希望使用不同的选项集,因为这可能导致额外的编译工作、减少计划重用并由于缓存中的多个计划副本而导致计划缓存膨胀。Using different sets of options is usually undesirable because it can cause extra compilations, less plan reuse, and plan cache inflation because of multiple copies of plans in the cache.

计算 Set 选项Evaluating Set Options

转换中返回的值set_options编译计划所用的选项,减去这些值set_options值,直到你从最大值,达到 0。To translate the value returned in set_options to the options with which the plan was compiled, subtract the values from the set_options value, starting with the largest possible value, until you reach 0. 所减去的每个值对应于查询计划中所用的一个选项。Each value you subtract corresponds to an option that was used in the query plan. 例如,如果中的值set_options为 251,编译计划所使用的选项为 ANSI_NULL_DFLT_ON (128)、 QUOTED_IDENTIFIER (64)、 ANSI_NULLS(32)、 ANSI_WARNINGS (16)、 CONCAT_NULL_YIELDS_NULL (8)、 并行 Plan(2)和 ANSI_PADDING (1)。For example, if the value in set_options is 251, the options the plan was compiled with are ANSI_NULL_DFLT_ON (128), QUOTED_IDENTIFIER (64), ANSI_NULLS(32), ANSI_WARNINGS (16), CONCAT_NULL_YIELDS_NULL (8), Parallel Plan(2) and ANSI_PADDING (1).

OptionOption ReplTest1Value
ANSI_PADDINGANSI_PADDING 11
Parallel PlanParallel Plan 22
FORCEPLANFORCEPLAN 44
CONCAT_NULL_YIELDS_NULLCONCAT_NULL_YIELDS_NULL 88
ANSI_WARNINGSANSI_WARNINGS 1616
ANSI_NULLSANSI_NULLS 3232
QUOTED_IDENTIFIERQUOTED_IDENTIFIER 6464
ANSI_NULL_DFLT_ONANSI_NULL_DFLT_ON 128128
ANSI_NULL_DFLT_OFFANSI_NULL_DFLT_OFF 256256
NoBrowseTableNoBrowseTable

指示计划不使用工作表实现 FOR BROWSE 操作。Indicates that the plan does not use a work table to implement a FOR BROWSE operation.
512512
TriggerOneRowTriggerOneRow

指示计划包含针对 AFTER 触发器 delta 表的单行优化。Indicates that the plan contains single row optimization for AFTER trigger delta tables.
10241024
ResyncQueryResyncQuery

指示查询由内部系统存储过程提交。Indicates that the query was submitted by internal system stored procedures.
20482048
ARITH_ABORTARITH_ABORT 40964096
NUMERIC_ROUNDABORTNUMERIC_ROUNDABORT 81928192
DATEFIRSTDATEFIRST 1638416384
DATEFORMATDATEFORMAT 3276832768
LanguageIDLanguageID 6553665536
UPONUPON

指示编译计划时数据库选项 PARAMETERIZATION 设置为 FORCED。Indicates that the database option PARAMETERIZATION was set to FORCED when the plan was compiled.
131072131072
ROWCOUNTROWCOUNT 适用于: SQL Server 2012 (11.x)SQL Server 2012 (11.x)SQL ServerSQL ServerApplies To: SQL Server 2012 (11.x)SQL Server 2012 (11.x) to SQL ServerSQL Server

262144262144

游标Cursors

不活动游标缓存在编译的计划中,以便游标的并发用户可以重用存储游标所使用的内存。Inactive cursors are cached in a compiled plan so that the memory used to store the cursor can be reused by concurrent users of cursors. 例如,假设某批处理声明并使用了一个游标,但未释放该游标。For example, suppose that a batch declares and uses a cursor without deallocating it. 如果有两个用户执行同一个批处理,将有两个活动游标。If there are two users executing the same batch, there will be two active cursors. 游标释放(可能在不同批处理中)后,用于存储该游标的内存就会缓存但不释放。Once the cursors are deallocated (potentially in different batches), the memory used to store the cursor is cached and not released. 此不活动游标列表保存在编译的计划中。This list of inactive cursors is kept in the compiled plan. 下次用户执行该批处理时,缓存的游标内存将重用并相应地初始化为活动游标。The next time a user executes the batch, the cached cursor memory will be reused and initialized appropriately as an active cursor.

计算游标选项Evaluating Cursor Options

转换中返回的值required_cursor_optionsacceptable_cursor_options编译计划所用的选项,减去的列值,并从开始值最大值,直到达到 0。To translate the value returned in required_cursor_options and acceptable_cursor_options to the options with which the plan was compiled, subtract the values from the column value, starting with the largest possible value, until you reach 0. 所减去的每个值对应于查询计划中所用的一个游标选项。Each value you subtract corresponds to a cursor option that was used in the query plan.

OptionOption ReplTest1Value
None 00
INSENSITIVEINSENSITIVE 11
SCROLLSCROLL 22
READ ONLYREAD ONLY 44
FOR UPDATEFOR UPDATE 88
LOCALLOCAL 1616
GLOBALGLOBAL 3232
FORWARD_ONLYFORWARD_ONLY 6464
KEYSETKEYSET 128128
DYNAMICDYNAMIC 256256
SCROLL_LOCKSSCROLL_LOCKS 512512
OPTIMISTICOPTIMISTIC 10241024
STATICSTATIC 20482048
FAST_FORWARDFAST_FORWARD 40964096
IN PLACEIN PLACE 81928192
FOR select_statement FOR select_statement 1638416384

示例Examples

A.A. 返回特定计划的属性Returning the attributes for a specific plan

下例将返回指定计划的所有计划属性。The following example returns all plan attributes for a specified plan. 将首先查询 sys.dm_exec_cached_plans 动态管理视图以获得指定计划的计划句柄。The sys.dm_exec_cached_plans dynamic management view is queried first to obtain the plan handle for the specified plan. 在第二次查询中,用第一次查询中的计划句柄值替换 <plan_handle>In the second query, replace <plan_handle> with a plan handle value from the first query.

SELECT plan_handle, refcounts, usecounts, size_in_bytes, cacheobjtype, objtype   
FROM sys.dm_exec_cached_plans;  
GO  
SELECT attribute, value, is_cache_key  
FROM sys.dm_exec_plan_attributes(<plan_handle>);  
GO  

B.B. 返回编译计划的 SET 选项和缓存计划的 SQL 句柄Returning the SET options for compiled plans and the SQL handle for cached plans

下例将返回代表编译每个计划时所用选项的值。The following example returns a value representing the options that each plan was compiled with. 此外,还将返回所有缓存计划的 SQL 句柄。In addition, the SQL handle for all the cached plans is returned.

SELECT plan_handle, pvt.set_options, pvt.sql_handle  
FROM (  
    SELECT plan_handle, epa.attribute, epa.value   
    FROM sys.dm_exec_cached_plans   
        OUTER APPLY sys.dm_exec_plan_attributes(plan_handle) AS epa  
    WHERE cacheobjtype = 'Compiled Plan') AS ecpa   
PIVOT (MAX(ecpa.value) FOR ecpa.attribute IN ("set_options", "sql_handle")) AS pvt;  
GO  

请参阅See Also

动态管理视图和函数 (Transact-SQL) Dynamic Management Views and Functions (Transact-SQL)
与执行相关的动态管理视图和函数(Transact SQL) Execution Related Dynamic Management Views and Functions (Transact-SQL)
sys.dm_exec_cached_plans (Transact SQL) sys.dm_exec_cached_plans (Transact-SQL)
sys.databases (Transact-SQL) sys.databases (Transact-SQL)
sys.objects (Transact-SQL)sys.objects (Transact-SQL)