sys.dm_exec_cached_plans (Transact-SQL)

项目
03/07/2014

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

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

适用范围：SQL Server（SQL Server 2008 至当前版本），Windows Azure SQL Database（初始版本至当前版本）。

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	存储过程
Prepared	预定义语句
Adhoc	即席查询 1
ReplProc	复制筛选过程
触发器	触发器
视图	视图
默认	默认
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，此计划内存使用量就是针对该资源池而言的。

1 使用 osql 或 sqlcmd 而不是作为远程过程调用引用作为语言事件提交的 Transact-SQL。

权限

要求对服务器拥有 VIEW SERVER 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 运算符将 set_options 和 sql_handle 属性输出为列而非行。有关 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, 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';
GO