“针对即席工作负荷进行优化”如何影响你的计划缓存

今天我想谈下SQL Server里的针对即席工作负荷进行优化（Optimize for Adhoc Workload）服务器配置选项，还有它如何影响你的计划缓存。在我们挖掘细节之前，我想向你展示下SQL Server如何重用缓存的执行计划。

执行计划重用

每次你提交一个查询给SQL Server，SQL Server检查计划缓存来看看是否有现存的缓存计划可供重用。SQL Server对提交的SQL语句计算哈希值，并用这个哈希值在计划缓存里检查现存的执行计划（实际或更复杂，因为其他的选项——例如SET选项——也会影响执行计划重用）。

如果缓存的计划找到，执行计划会重用。不然的话新的执行计划会通过查询优化器编译，最后放入计划作为后期重用。现在假设有下列写的很烂的C#应用程序。 

for (int i = 1; i <= 10000; i++)
{
   cmd = new SqlCommand(
   "SELECT * FROM Sales.SalesOrderDetail WHERE SalesOrderDetailID = " + i.ToString(), cnn);
   SqlDataReader reader = cmd.ExecuteReader();
   reader.Close();
}

这个程序在loop循环里调用了1000次SELECT语句——使用硬编码参数值。使用硬编码参数值，SQL语句的哈希值总会不一样，因此SQL Server不能重用缓存的计划。作为副作用，你在计划缓存里存储了1000个不同的执行计划。

每个执行计划也会占用一些内存（在这里是16KB），因此你浪费了近160M的计划缓存！假设现在你不能修改你程序的实现方法，你还是要提高SQL Server里计划缓存的内存占用。这就要用到“针对即席工作负荷进行优化”服务器配置选项。 

针对即席工作负荷进行优化（Optimize for Adhoc Workload）

这个服务器配置选项自SQL Server 2008后就引入了，它用来改变SQL Server缓存和重用执行计划方式。一旦你启用这个配置选项，SQL Server并不再真正缓存你的整个执行计划。SQL Server只存储所谓的编译计划存根（Compiled Plan Stub）。在SQL Server 2014上，对你的SQL语句，存根值是352 bytes的哈希值。

这样的话对于10000个提交的SQL语句现在我们在计划缓存里只需要7MB的内存。这和刚才的160MB相比已经是巨大的区别！使用这个存根值，SQL Server现在能记住先前执行的特定SQL语句。

现在当你再次执行同一SQL语句是，SQL Server在执行计划里找存根值，再次编译你的执行计划，最后在计划缓存里保存完整执行计划结果。因此当SQL语句被执行至少2次时，这个执行计划才会被缓存。SQL语句只执行一次的话（即席SQL语句），在计划缓存里只需要352 bytes。

因此使用这个配置选项你可以减少计划缓存的内存占用——在你的程序里不需要任何修改！可以看下计划缓存文章来了解更多。

小结

在这篇文章里我向你展示了一个非常简单的方法来处理对于写得很糟的应用程序，它用不同的不能重用的执行计划污染你的计划缓存。“针对即席工作负荷进行优化”服务器配置选项也是我经常建议默认启用的。它会他来计划缓存更好的内存管理。

副作用是你引入的轻量的CPU负担，因为每个执行计划必须编译2次才会存储在计划缓存里。当一般来说对于这个额外编译，你应该有足够的CPU可用空间。

感谢关注！ 

参考文章：

https://www.sqlpassion.at/archive/2015/07/13/how-optimize-for-adhoc-workload-impacts-your-plan-cache/