sys.dm_exec_cached_plans (Transact-SQL)

适用于:yesSQL Server(所有受支持的版本)YesAzure SQL 数据库YesAzure SQL 托管实例yesAzure Synapse AnalyticsyesAnalytics Platform System (PDW)

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

在Azure SQL 数据库中,动态管理视图无法公开影响数据库包含或公开用户有权访问的其他数据库的信息。 为了避免公开此信息,将筛选出包含不属于已连接租户的数据的每一行。此外,将筛选 列memory_object_addresspool_id 中的值;列值设置为 NULL。

注意

若要从 Azure Synapse Analytics 或 Analytics Platform System (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 引用该缓存对象的缓存对象数。 如果要使条目存在于缓存中,Refcounts 必须至少为 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:即席查询。 指通过使用 osqlsqlcmd 而不是远程过程调用作为语言事件提交的 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”服务目标中,对于“弹性池”中的数据库,服务器管理员帐户、Azure Active Directory 管理员帐户或##MS_ServerStateReader##服务器角色中的成员身份是必需的。 对于所有其他 SQL 数据库服务目标,需要数据库的 VIEW DATABASE STATE 权限或 ##MS_ServerStateReader## 服务器角色中的成员身份。

示例

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)