谈一谈SQL Server中的执行计划缓存（下）

简介 在上篇文章中我们谈到了查询优化器和执行计划缓存的关系，以及其二者之间的冲突。本篇文章中，我们会主要阐述执行计划缓存常见的问题以及一些解决办法。 将执行缓存考虑在内时的流程 上篇文章中提到了查询优化器解析语句的过程，当将计划缓存考虑在内时

简介

    在上篇文章中我们谈到了查询优化器和执行计划缓存的关系，网站空间，以及其二者之间的冲突。本篇文章中，我们会主要阐述执行计划缓存常见的问题以及一些解决办法。

 

将执行缓存考虑在内时的流程

    上篇文章中提到了查询优化器解析语句的过程，当将计划缓存考虑在内时，首先需要查看计划缓存中是否已经有语句的缓存，如果没有，才会执行编译过程，如果存在则直接利用编译好的执行计划。因此，完整的过程如图1所示。

图1.将计划缓存考虑在内的过程

 

    图1中我们可以看到，其中有一步需要在缓存中找到计划的过程。因此不难猜出，只要是这一类查找，一定跑不了散列（Hash）的数据结构。通过sys.dm_os_memory_cache_hash_tables这个DMV可以找到有关该Hash表的一些信息，如图2所示。这里值得注意的是，当执行计划过多导致散列后的对象在同一个Bucket过多时，则需要额外的Bucket，因此可能会导致查找计划缓存效率低下。解决办法是尽量减少在计划缓存中的计划个数，我们会在本文后面讨论到。

图2.有关存储计划缓存的HashTable的相关信息

 

    当出现这类问题时，我们可以在buckets_avg_scan_miss_length列看出问题。这类情况在缓存命中率（SQL Server: Plan Cache-Cache Hit Ratio）比较高，但编译时间过长时可以作为考虑对象。

 

参数化和非参数化

    查询计划的唯一标识是查询语句本身，但假设语句的主体一样，而仅仅是查询条件谓词不一样，那在执行计划中算1个执行计划还是两个执行计划呢？It’s Depends。

    假设下面两个语句,如图3所示。

图3.仅仅谓词条件不一样的两个语句

 

    虽然执行计划一样，但是在执行计划缓存中却会保留两份执行计划，如图4所示。

图4.同一个语句，不同条件，有两份不同的执行计划缓存

 

    我们知道，执行计划缓存依靠查询语句本身来判别缓存，因此上面两个语句在执行计划缓存中就被视为两个不同的语句。那么解决该问题的手段就是使得执行计划缓存中的查询语句一模一样。

 

参数化

    使得仅仅是某些参数不同，而查询本身相同的语句可以复用，就是参数化的意义所在。比如说图3中的语句，如果我们启用了数据库的强制参数化，香港服务器租用，或是使用存储过程等。SQL Server会将这些语句强制参数话，比如说我们根据图5修改了数据库层级的选项。

图5.数据库层级的选项

 

   此时我们再来执行图3中的两条语句，通过查询执行计划缓存，我们发现变量部分被参数化了，从而在计划缓存中的语句变得一致，如图6所示，从而可以复用.

图6.参数话之后的查询语句

 

   但是，强制参数会引起一些问题，查询优化器很多时候就无法根据统计信息最优化一些具体的查询，比如说不能应用一些索引或者该扫描的时候却查找。所产生的负面影响在上篇文章中已经说过，这里就不细说了。

   因此对于上面的问题可以有几种解决办法。

 

平衡参数化和非参数化

    在具体的情况下，参数化有些时候是好的,但有些时候却是性能问题的罪魁祸首，下面我们来看几种平衡这两者之间关系的手段。

 

使用RECOMPILE

    当查询中，虚拟主机，不准确的执行计划的成本要高于编译的成本时，在存储过程中使用RECOMPILE选项或是在即席查询中使用RECOMPILE提示使得每次查询都会重新生成执行计划，该参数会使得生成的执行计划不会被插入到执行计划缓存中。对于OLAP类查询来说，不准确的执行计划所耗费的成本往往高于编译成本太多，所以可以考虑该参数或选项，您可以如代码清单1中的查询所示这样使用Hint。

SELECT * FROM Sales.Customer WHERE CustomerID>20000 AND TerritoryID = 4 OPTION (recompile)