sys.dm_exec_plan_attributes
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.
Note
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_handle
Uniquely identifies a query plan for a batch that has executed and whose plan resides in the plan cache. plan_handle is varbinary(64). 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)
Name of the attribute associated with this plan. One of the following:
Attribute
Data type
Description
set_optionsintIndicates the option values that the plan was compiled with.
objectidintOne of the main keys used for looking up an object in the cache. 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.
dbidintIs the ID of the database where the entity for which we have the plan resides. For ad hoc or prepared plans, it is the database ID from which the batch is executed.
dbid_executeintFor system objects stored in the Resource database, the database ID from which the cached plan is executed. For all other cases it is 0.
user_idintID of the schema that contains the object. A 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.
language_idsmallintID of the language of the connection that created the cache object. For more information, see sys.syslanguages (Transact-SQL).
date_formatsmallintDate format of the connection that created the cache object. For more information, see SET DATEFORMAT (Transact-SQL).
date_firsttinyintDate first value. For more information, see SET DATEFIRST (Transact-SQL).
statusintInternal status bits that are part of the cache lookup key.
required_cursor_optionsintCursor options specified by the user such as the cursor type.
acceptable_cursor_optionsintCursor options that SQL 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. For more information, see Using Implicit Cursor Conversions.
inuse_exec_contextintNumber of currently executing batches that are using the query plan. For more information about execution context and query plans, see Execution Plan Caching and Reuse.
free_exec_contextintNumber of cached execution contexts for the query plan that are not being currently used.
hits_exec_contextintNumber 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_contextintNumber 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_contextintNumber of execution contexts that have been removed because of memory pressure on the cached plan.
inuse_cursorsintNumber of currently executing batches containing one or more cursors that are using the cached plan.
free_cursorsintNumber of idle or free cursors for the cached plan.
hits_cursorsintNumber 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_cursorsintNumber of times that an inactive cursor could not be found in the cache.
removed_cursorsintNumber of cursors that have been removed because of memory pressure on the cached plan.
sql_handlevarbinary(64)The SQL handle for the batch.
value
sql_variant
Value of the attribute that is associated with this plan.
is_cache_key
bit
Indicates whether the attribute is used as part of the cache lookup key for the plan.
Remarks
Set Options
Copies of the same compiled plan might differ only by the value in the set_options column. 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. For more information, see Query Tuning Recommendations.
Evaluating Set Options
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. 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).
| Option | Value |
|---|---|
ANSI_PADDING |
1 |
Parallel Plan |
2 |
FORCEPLAN |
4 |
CONCAT_NULL_YIELDS_NULL |
8 |
ANSI_WARNINGS |
16 |
ANSI_NULLS |
32 |
QUOTED_IDENTIFIER |
64 |
ANSI_NULL_DFLT_ON |
128 |
ANSI_NULL_DFLT_OFF |
256 |
NoBrowseTable Indicates that the plan does not use a work table to implement a FOR BROWSE operation. |
512 |
TriggerOneRow Indicates that the plan contains single row optimization for AFTER and INSTEAD OF triggers. That is, if only one row is affected in the inserted or deleted tables, this option is set. Applies to Transact-SQL triggers and CLR triggers that perform in-process data access. |
1024 |
ResyncQuery Indicates that the query was submitted by internal system stored procedures. |
2048 |
ARITH_ABORT |
4096 |
NUMERIC_ROUNDABORT |
8192 |
DATEFIRST |
16384 |
DATEFORMAT |
32768 |
LanguageID |
65536 |
UPON Indicates that the database option PARAMETERIZATION was set to FORCED when the plan was compiled. |
131072 |
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
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.
| Option | Value |
|---|---|
None |
0 |
INSENSITIVE |
1 |
SCROLL |
2 |
READ ONLY |
4 |
FOR UPDATE |
8 |
LOCAL |
16 |
GLOBAL |
32 |
FORWARD_ONLY |
64 |
KEYSET |
128 |
DYNAMIC |
256 |
SCROLL_LOCKS |
512 |
OPTIMISTIC |
1024 |
STATIC |
2048 |
FAST_FORWARD |
4096 |
IN PLACE |
8192 |
FOR select_statement |
16384 |
Permissions
Requires VIEW SERVER STATE permission on the server.
Examples
A. Returning the attributes for a specific plan
The following example returns all plan attributes for a specified plan. The sys.dm_exec_cached_plans dynamic management view is queried first to obtain the plan handle for the specified plan. 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. 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. In addition, the SQL text and SQL handle are returned.
SELECT plan_handle, pvt.set_options, pvt.sql_handle, text
FROM (
SELECT plan_handle, epa.attribute, epa.value, st.text
FROM sys.dm_exec_cached_plans
OUTER APPLY sys.dm_exec_plan_attributes(plan_handle) AS epa
CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS st
WHERE cacheobjtype = 'Compiled Plan') AS ecpa
PIVOT (MAX(ecpa.value) FOR ecpa.attribute IN ("set_options", "sql_handle")) AS pvt;
GO
See Also
Reference
Dynamic Management Views and Functions
Execution Related Dynamic Management Views and Functions
sys.dm_exec_cached_plans
sys.databases (Transact-SQL)
sys.objects (Transact-SQL)
Help and Information
Getting SQL Server 2005 Assistance
Change History
| Release | History |
|---|---|
12 December 2006 |
|