3 .6 .5 优化Ad-Hoc工作负载

执行计划生成后会存储在plan cache中，以便重用，如果计划缓存从来都没有被重用 过，将会造成内存资源的浪费，这有可能是由于非参数化的Ad-hoc (即席查询）引起的。 当执行代码时，会产生一个hash值，用于匹配计划缓存中的hash值，相同的hash值代表 语句是相同的。如果执行一个存储过程，会按照存储过程名来创建hash值，如果在存储过 程之外执行代码（Ad-hoc T-SQL),那么hash值会根据整个语句产生。你的代码有一点点字 面上的改变，都会产生不同的hash值，导致计划无法重用。当有大量Ad-hoc执行时，会 导致计划缓存的膨胀。

针对这类问题，可以考虑使用存储过程、函数或者参数化Ad-hoc,但是有时候的确没 有办法，必须使用非参数化的Ad-hoc。从 SQL Server 2008开始，引人了一个“针对即席 工作负荷进行优化”的选项，如图3-9所示。

找到该选项的具体步骤是：右键实例，然后选择“属性"，再选择“高级"，之后把图3-9

箭头处的False改成True。下面是针对该选项的官方解释：

 

“针对即席工作负荷进行优化”选项用于提高包含许多一次性临时批处理的工作负荷计

划缓存的效率。如果该选项设置为True，则数据库引擎将在首次编译批处理时在计划缓存 中存储一个编译的小计划存根，而不是存储完全编译的计划。在这种情况下，不会让未重

复使用的编译计划填充计划缓存，从而有助于缓解内存压力。

编译的计划存根使数据库引擎能够识别此临时批处理以前已经编译过，但只存储了编

译计划存根，因此当再次调用（编译或执行）此批处理时，数据库引擎会对此批处理进行编

译，从计划缓存中删除编译计划存根并将完全编译的计划添加到计划缓存中。

将 “针对即席工作负荷进行优化”设置为1 只会影响新计划，而已在计划缓存中的计

划不受影响。

编译计划存根是sys.dm_exec_cached_plans目录视图显示的cacheobjtype之一。它具有

唯一的SQL句柄和计划句柄。编译计划存根没有与其关联的执行计划，并且查询计划句柄

不会返回XML显示计划。

可以用下面的脚本来查看缓存对象的对应内存数。

SELECT objtype AS 'Cached Object Type * , COUNT(*) AS 'Number of Plans', SUM(CAST{size_in_bytes AS BIGINT)) / 1024 / 1024 AS 'Plan Cache Size (MB)', AVG(usecounts) AS 'Avg Use Count * FROM sys.dm_exec_cached_plans GROUP BY objtype

在笔者计算机上的结果如图3-10所示。

 

 在 没 有 开 启 上 面 选 项 的 系 统 中 ，Ad-hoc通 常 是 内 存 占 用 最 多 的 部 分 。所 以 从 SQL Server 2008开 始 ，建议开启这个选项。