sys.dm_exec_cached_plans (Transact-SQL)

项目
12/19/2023

适用于：SQL Server Azure SQL 数据库 Azure SQL 托管实例 Azure Synapse Analytics Analytics 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) 此分发所在节点的标识符。

权限

对于 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)

通过