Applies To: SQL Server 2014, SQL Server 2016 Preview
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.
Some of the information returned through this function maps to the sys.syscacheobjects backward compatibility view.
Applies to: SQL Server (SQL Server 2008 through current version), sqldbesa.
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.
Name of the attribute associated with this plan. One of the following:
Value of the attribute that is associated with this plan.
Indicates whether the attribute is used as part of the cache lookup key for the plan.
On SQL Server requires VIEW SERVER STATE permission on the server.
On SQL Database Premium Tiers requires the VIEW DATABASE STATE permission in the database. On SQL Database Standard and Basic Tiers requires the SQL Database admin account.
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.
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).
Indicates that the plan does not use a work table to implement a FOR BROWSE operation.
Indicates that the plan contains single row optimization for AFTER trigger delta tables.
Indicates that the query was submitted by internal system stored procedures.
Indicates that the database option PARAMETERIZATION was set to FORCED when the plan was compiled.
Applies To: SQL Server 2012 to SQL Server 2016
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.
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.
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
The following example returns a value representing the options that each plan was compiled with. 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