SQLSERVER .计划缓存

5.计划缓存

生成查询执行计划的过程非常消耗时间和资源，因此，如果SQL Server已经为一段代码生成了很好的执行计划，那很直观的想法就是将这个执行计划缓存起来以供后续重用。

计划缓存的作用是对所有的执行计划进行缓存以方便重用它们。

可以通过动态管理视图sys.dm_exec_cached_plans查看计划缓存的内容及其占用的空间。也可以通过执行DBCC MEMORYSTATUS命令查看输出的“Procedure Cache”部分的内容查看以上信息，这一部分包含了计划缓存中的执行计划的数目以及缓存占用的8KB页面的数目。

下面这个示例脚本使用了sys.dm_exec_cached_plans动态管理视图，输出了缓存的计划数目以及这些计划缓存占用的空间，单位为MB。

SELECT count(*) AS 'Number of Plans',
sum(cast(size_in_bytes AS BIGINT))/1024/1024AS 'Plan Cache Size (MB)'
FROM sys.dm_exec_cached_plans

在一个客户的配置了2GB内存的生产环境的SQL Server系统上执行上面的脚本，输出结果如下：

Number of Plans Plan Cache Size (MB)
8140 848

这个结果表明这台服务器上的计划缓存占用的空间为848MB，这在一台只有2GB内存的服务器上是很可观的。性能监视器中的Page Life Expectancy计数器值超过了28 000秒，因此可以看出这台服务器上的缓冲池压力并不大。尽管如此，还是有必要研究一下是否真有必要使用这么大的计划缓存。

运行下面的脚本，将计划缓存的空间分配到不同类型的缓存对象：

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

结果如下：

Cached Object Type Number of Plans Plan Cache Size (MB) Avg Use Count
View 108 7 57
Check 2 0 5
UsrTab 1 0 2
Prepared 24 2 11
Adhoc 7796 808 1
Trigger 2 0 20
Proc 207 31 84

从结果中可以看出，计划缓存的大部分空间都被Adhoc计划占用了，且平均使用次数为1，也就是说，这些计划从来没有得到重用。

Adhoc计划是在存储过程、函数或触发器上下文之外执行T-SQL命令的时候创建的，这些计划创建了之后很少被重用。这种Adhoc计划的情形经常发生，是因为应用程序使用了很多动态生成的SQL语句，而没有使用存储过程。

如果应用程序的设计使得很少使用存储过程(甚至参数化的查询)，那么在这种情况下应该监视Adhoc计划的空间占用情况。如果SQL Server的其他组件例如数据缓存开始产生内存紧张的时候应该采取其他处理办法，例如为服务器添加更多的物理内存，或定期清除缓存中的Adhoc计划，或者使用sp_configure的新选项OPTIMIZE FOR ADHOC WORKLOADS。

执行下面的语句可以从缓存中清除Adhoc计划(很遗憾预处理计划(prepared plan)也被清除了，不过存储过程的执行计划被保留了，保留存储过程的执行计划更为重要)，从而释放内存：

DBCC FREESYSTEMCACHE('SQL Plans')

当然，执行这个命令并不能阻止Adhoc计划再次占用缓存空间；如果要真正根除它，必须把这些生成Adhoc计划的代码改写为存储过程或参数化的查询。清除Adhoc计划之后，缓存占用情况如下所示：

Cached Object Type Number of Plans Plan Cache Size (MB) Avg Use Count
View 108 7 57
Check 2 0 5
UsrTab 1 0 2
Adhoc 13 0 1
Trigger 2 0 20
Proc 207 31 84

执行这个命令之后，计划缓存只占用了38MB的空间。第5章将详细描述计划重用。

SQL Server通过下面的规则计算计划缓存占用的最大空间：

服务器内存的0～4GB部分的75%+

服务器内存的4GB～64GB部分的10%+

服务器内存超过64GB部分的5%

例如，如果一个系统配置了16GB的内存，那么这台服务器上最大计划缓存为3GB + 1.2GB = 4.2GB。