Home Microsoft SQL Server DigiMailing iRN Contact
    Keyword



sys.dm_exec_cached_plans

 

This is a view in the mssqlsystemresource database. Similar to syscacheobjects in SQL Server 2000. Returns a row for each query plan that is held in procedure cache, and containing information like the cached query plans, the cached query text, the amount of memory taken by cached plans, and the reuse count of the cached plans. For query plans, the sys.dm_exec_cached_plans dynamic management view maps to the syscacheobjects system table in SQL Server 2000. 

Column name Data type Description

bucketid

int

ID of the hash bucket in which the entry is cached. The value indicates a range from 0 through the hash table size for the type of cache.

For the SQL Plans and Object Plans caches , the hash table size can be up to 10007 on 32-bit systems and up to 40009 on 64-bit systems. For the Bound Trees cache, the hash table size can be up to 1009 on 32-bit systems and up to 4001 on 64-bit systems. For the Extended Stored Procedures cache the hash table size can be up to 127 on 32-bit and 64-bit systems. For more information about cache types and hash tables, see sys.dm_os_memory_cache_hash_tables.

refcounts

int

Number of cache objects that are referencing this cache object. Refcounts must be at least 1 for an entry to be in the cache.

usecounts

int

Number of times this cache object has been used since its inception.

size_in_bytes

int

Number of bytes consumed by the cache object.

memory_object_address

varbinary(8)

Memory address of the cached entry. This value can be used with sys.dm_os_memory_objects to get the memory breakdown of the cached plan and with sys.dm_os_memory_cache_entries_entries to obtain the cost of caching the entry.

cacheobjtype

nvarchar(34)

Type of object in the cache. The value can be one of the following:

  • Compiled Plan
     
  • Parse Tree
     
  • Extended Proc
     
  • CLR Compiled Func
     
  • CLR Compiled Proc
     

objtype

nvarchar(16)

Type of object. The value can be one of the following:

Value Description

plan_handle

varbinary(64)

Identifier for the in-memory plan. This identifier is transient and remains constant only while the plan remains in the cache. This value may be used with the following dynamic management functions:
sys.dm_exec_sql_text
sys.dm_exec_query_plan
sys.dm_exec_plan_attributes

1  Refers to Transact-SQL submitted as language events by using osql or sqlcmd instead of as remote procedure calls.

A. Returning the batch text of cached entries that are reused

The following example returns the SQL text of all cached entries that have been used more than once.

SELECT usecounts, cacheobjtype, objtype, text FROM sys.dm_exec_cached_plans CROSS APPLY sys.dm_exec_sql_text(plan_handle) WHERE usecounts > 1 ORDER BY usecounts DESC

B. Returning query plans for all cached triggers

The following example returns the query plans of all cached triggers.

SELECT plan_handle, query_plan, objtype FROM sys.dm_exec_cached_plans CROSS APPLY sys.dm_exec_query_plan(plan_handle) WHERE objtype ='Trigger'

C. Returning the SET options with which the plan was compiled

The following example returns the SET options with which the plan was compiled. The sql_handle for the plan is also returned. The PIVOT operator is used to output the set_options and sql_handle attributes as columns rather than as rows. For more information about the value returned in set_options, see sys.dm_exec_plan_attributes.

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
 

D. Returning the memory breakdown of all cached compiled plans

The following example returns a breakdown of the memory used by all compiled plans in the cache.

SELECT plan_handle, ecp.memory_object_address AS CompiledPlan_MemoryObject, omo.memory_object_address, pages_allocated_count, type, page_size_in_bytes FROM sys.dm_exec_cached_plans AS ecp JOIN sys.dm_os_memory_objects AS omo ON ecp.memory_object_address = omo.memory_object_address OR ecp.memory_object_address = omo.parent_address WHERE cacheobjtype = 'Compiled Plan'

 




Syntax

create view sys.dm_exec_cached_plans as select * from OpenRowset(TABLE SYSDMEXECCACHEDPLANS)

 
Last revision 2008RTM
See also

  syscacheobjects (View)
       



News

  Query a named instance
Sybase+ASE+silent+install
SQL 2008 R2
Deprecated procedures in SQL2008
Reporting Services item-level role definitions
Create all your missing indexes
Converting datetime field
Start MSSQL Server Profiler at time
Replicating MSSQL Server views
Exploring Microsoft Sharepoint
The OLE DB provider "SQLNCLI10" for linked server indicates.
Mobile solar charger
Oracle to SQL Server replication
Cannot insert the value NULL into column
Undocumented Microsoft SQL Server 2008
VMware
Zoekmachine optimalisatie
SQL Servers hidden objects
FckEditor
Flash in Ajax
De hype die AJAX heet
Barcode scannen
MySQL; Gratis, makkelijk en snel!
PHP
sIFR; de combinatie tussen HTML en Flash