Skip to content

sys-dm-exec-cached-plans-transact-sql.md

Latest commit

 

History

History
130 lines (106 loc) · 8.65 KB

sys-dm-exec-cached-plans-transact-sql.md

File metadata and controls

130 lines (106 loc) · 8.65 KB
title sys.dm_exec_cached_plans (Transact-SQL)
description sys.dm_exec_cached_plans (Transact-SQL)
author rwestMSFT
ms.author randolphwest
ms.date 12/16/2025
ms.service sql
ms.subservice system-objects
ms.topic reference
ms.custom
ignite-2025
f1_keywords
sys.dm_exec_cached_plans_TSQL
sys.dm_exec_cached_plans
dm_exec_cached_plans_TSQL
dm_exec_cached_plans
helpviewer_keywords
sys.dm_exec_cached_plans dynamic management view
dev_langs
TSQL
monikerRange =azuresqldb-current || >=sql-server-2016 || >=sql-server-linux-2017 || =azuresqldb-mi-current || >=aps-pdw-2016 || =azure-sqldw-latest || =fabric-sqldb

sys.dm_exec_cached_plans (Transact-SQL)

[!INCLUDE SQL Server SQL Database-fabricsqldb]

Returns a row for each query plan that is cached by [!INCLUDE ssNoVersion] for faster query execution. You can use this dynamic management view to find cached query plans, cached query text, the amount of memory taken by cached plans, and the reuse count of the cached plans.

In [!INCLUDE ssazure-sqldb], dynamic management views can't expose information that would impact database containment or expose information about other databases the user has access to. To avoid exposing this information, every row that contains data that doesn't belong to the connected tenant is filtered out. In addition, the values in the columns memory_object_address and pool_id are filtered; the column value is set to NULL.

Note

To call this from [!INCLUDE ssazuresynapse-md] or [!INCLUDE ssPDW], use the name sys.dm_pdw_nodes_exec_cached_plans. [!INCLUDE synapse-analytics-od-unsupported-syntax]

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 10,007 on 32-bit systems and up to 40,009 on 64-bit systems. For the Bound Trees cache, the hash table size can be up to 1,009 on 32-bit systems and up to 4,001 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.
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 the cache object has been looked up. Not incremented when parameterized queries find a plan in the cache. Can be incremented multiple times when using showplan.
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:

Compiled Plan
Compiled Plan Stub
Parse Tree
Extended Proc
CLR Compiled Func
CLR Compiled Proc
objtype nvarchar(16) Type of object. Below are the possible values and their corresponding descriptions.

Proc: Stored procedure
Prepared: Prepared statement
Ad hoc: Ad hoc query. Refers to [!INCLUDE tsql] submitted as language events by using osql or sqlcmd instead of as remote procedure calls.
ReplProc: Replication-filter-procedure
Trigger: Trigger
View: View
Default: Default
UsrTab: User table
SysTab: System table
Check: CHECK constraint
Rule: Rule
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 might be used with the following dynamic management functions:

sys.dm_exec_sql_text
sys.dm_exec_query_plan
sys.dm_exec_plan_attributes
pool_id int The ID of the resource pool against which this plan memory usage is accounted for.
pdw_node_id int The identifier for the node that this distribution is on.

Applies to: [!INCLUDE ssazuresynapse-md], [!INCLUDE ssPDW]

Permissions

[!INCLUDE sssql19-md] and earlier versions require VIEW SERVER STATE permission.

[!INCLUDE sssql22-md] and later versions, and [!INCLUDE ssazuremi-md], require VIEW SERVER PERFORMANCE STATE permission.

On [!INCLUDE ssazure-sqldb] Basic, S0, and S1 service objectives, and for databases in elastic pools, the server admin account, the Microsoft Entra admin account, or membership in the ##MS_ServerStateReader## server role is required. On all other SQL Database service objectives, either the VIEW DATABASE STATE permission on the database, or membership in the ##MS_ServerStateReader## server role is required.

Examples

A. Return 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. Return 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. Return 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. Return 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,
       type,
       page_size_in_bytes
FROM sys.dm_exec_cached_plans AS ecp
     INNER 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';

Related content