sys.dm_exec_cached_plans (Transact-SQL)

  • 项目

适用于：SQL ServerAzure SQL 数据库Azure SQL 托管实例Azure Synapse AnalyticsAnalytics Platform System (PDW)

为 SQL Server 缓存的每个查询计划返回一行，以便更快地执行查询。 可以用此动态管理视图来查找缓存的查询计划、缓存的查询文本、缓存计划占用的内存量，以及重新使用缓存计划的计数。

在 Azure SQL 数据库中，动态管理视图不能公开将影响数据库包含的信息，也不能公开有关用户可以访问的其他数据库的信息。 为了避免公开此信息，将筛选出包含不属于已连接租户的数据的每一行。此外，将筛选列memory_object_address和pool_id中的值;列值设置为 NULL。

注意

若要从 Azure Synapse Analytics 或 Analytics 平台系统（PDW）调用此名称，请使用名称 sys.dm_pdw_nodes_exec_cached_plans。 Azure Synapse Analytics 中的无服务器 SQL 池不支持此语法。

列名称 数据类型 描述
bucketid int 其中条目已缓存的哈希存储桶的 ID。 此值指示从 0 到特定缓存类型的哈希表大小之间的范围。

对于 SQL 计划和对象计划缓存而言，在 32 位系统上哈希表的大小可达 10007，在 64 位系统上哈希表的大小可达 40009。 对于绑定树缓存而言，在 32 位系统上哈希表大小可达 1009，在 64 位系统上哈希表大小可达 4001。 对于扩展存储过程缓存，哈希表大小在 32 位和 64 位系统上最多可为 127。
refcounts int 引用该缓存对象的缓存对象数。 引用计数 必须至少为 1，才能在缓存中输入。
usecounts int 已查找缓存对象的次数。 当参数化查询在缓存中找到计划时不递增。 在使用显示计划时可多次递增。
size_in_bytes int 缓存对象占用的字节数。
memory_object_address varbinary(8) 缓存条目的内存地址。 此值可用于 sys.dm_os_memory_objects 来获取缓存计划的内存细分，以及 sys.dm_os_memory_cache_entries_entries获取缓存条目的成本。
cacheobjtype nvarchar（34） 缓存中的对象类型。 值可以是下列任一值：

Compiled Plan

Compiled Plan Stub

Parse Tree

Extended Proc

CLR Compiled Func

CLR Compiled Proc
objtype nvarchar(16) 对象的类型。 下面是可能的值及其相应的说明。

Proc：存储过程
准备：准备语句
Adhoc：即席查询。 指使用 osql 或 sqlcmd 而不是远程过程调用作为语言事件提交的 Transact-SQL。
ReplProc：Replication-filter-procedure
触发器：触发器
视图：视图
默认值：默认值
UsrTab：用户表
SysTab：系统表
检查：CHECK 约束
规则：规则
plan_handle varbinary(64) 内存中计划的标识符。 该标识符是瞬态的，仅当计划保留在缓存中时，它才保持不变。 此值可以和以下动态管理函数一起使用：

sys.dm_exec_sql_text

sys.dm_exec_query_plan

sys.dm_exec_plan_attributes
pool_id int 特定资源池的 ID，此计划内存使用量就是针对该资源池而言的。
pdw_node_id int 适用于：Azure Synapse Analytics、Analytics Platform System (PDW)

此分发所在节点的标识符。

1

权限

对于 SQL Server 和 SQL 托管实例，需要 VIEW SERVER STATE 权限。

在SQL 数据库基本、S0 和 S1 服务目标以及弹性池中的数据库、服务器管理员帐户、Microsoft Entra 管理员帐户或服务器角色的成员##MS_ServerStateReader##身份是必需的。 对于所有其他 SQL 数据库服务目标，需要数据库的 VIEW DATABASE STATE 权限或 ##MS_ServerStateReader## 服务器角色中的成员身份。

SQL Server 2022 及更高版本的权限

要求对服务器具有 VIEW SERVER PERFORMANCE STATE 权限。

示例

A. 返回重新使用的缓存条目的批处理文本

以下示例返回经过多次使用的所有缓存条目的 SQL 文本。

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;  
GO

B. 为所有缓存触发器返回查询计划

以下示例返回所有缓存触发器的查询计划。

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

°C 返回编译计划所用的 SET 选项

以下示例返回编译计划所用的 SET 选项。 sql_handle还会返回计划。 PIVOT 运算符用于将列和sql_handle属性输出set_options为列而不是行。 有关返回set_options的值的详细信息，请参阅sys.dm_exec_plan_attributes（Transact-SQL）。

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

D. 返回所有缓存的编译计划的内存明细

以下示例返回缓存中所有编译计划所使用的内存明细。

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   
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';  
GO

另请参阅

动态管理视图和函数 (Transact-SQL)
与执行有关的动态管理视图和函数 (Transact-SQL)
sys.dm_exec_query_plan （Transact-SQL）
sys.dm_exec_plan_attributes （Transact-SQL）
sys.dm_exec_sql_text (Transact-SQL)
sys.dm_os_memory_objects （Transact-SQL）
sys.dm_os_memory_cache_entries （Transact-SQL）
FROM (Transact-SQL)