第10/24周 计划缓存

在这一期的性能调优培训里，我想详细谈下SQL Server里计划缓存及其副作用。在上一周你已经学到，每个提交给SQL Server的逻辑查询会编译成物理执行计划。那个执行计划然后会被缓存，即被称为计划缓存，用作后期的重用。首先我们来看下即席SQL语句（adhoc SQL statements，对应的反义词：prepared SQL statements）的副作用，即带来的性能问题。

即席SQL语句（adhoc SQL statements）

每次当你提交一个即席SQL语句到SQL Server时，对每个唯一的查询，都会有执行计划被编译。“唯一的查询”是什么意思？答案很简单：SQL Server对完整的SQL语句（包括可能硬编码的参数值）生成一个hash值，并使用这个hash值作为在计划缓存里查找值。如果找到这个值的执行计划，这个计划就会被重用，否则的话新的计划会被编译并最后在计划缓存里缓存。看下我们提交下面这3个查询给SQL Server： 

--清空计划缓存
DBCC FREEPROCCACHE;

SELECT * FROM Sales.SalesOrderHeader
WHERE CustomerID = 11000
GO

SELECT * FROM Sales.SalesOrderHeader
WHERE CustomerID = 30052
GO

SELECT * FROM Sales.SalesOrderHeader
WHERE CustomerID = 11223
GO
--查看缓存的执行计划
SELECT * FROM sys.dm_exec_cached_plans

--查看对应plan_handle的对应SQL语句
SELECT * FROM sys.dm_exec_sql_text(0x06000B0028382622B800CB0A000000000000000000000000)
SELECT * FROM sys.dm_exec_sql_text(0x06000B00E2CE4D15B820AE0A000000000000000000000000)
SELECT * FROM sys.dm_exec_sql_text(0x06000B005C407727B820C90A000000000000000000000000)
SELECT * FROM sys.dm_exec_sql_text(0x06000B00B4D38136B8200908000000000000000000000000)

 

  

对这3个查询，SQL Server会编译3个不同的执行计划，因为你提供硬编码的参数值。因此计算出来的hash值在3个查询之间是不同的，找不到被缓存的计划。作为一个副作用，对于几乎相同的查询，你有3个执行计划。这个问题被称为计划缓存污染（Plan Cache Pollution） 。

你用不同的执行计划污染了你的计划缓存，这些计划是不能被重用的（因为硬编码的参数值），并且你在浪费大量有用的内存，这些内存在SQL Server里可以被其他组件使用。缓存的目的应该是持续数次的高重用，特定SQL语句不属于这个情况。

计划稳定性

如果你参数话你的SQL语句，或者使用存储过程。在那个情况下，SQL Server可以非常容易的重用执行计划。但是即使重用执行计划也会带来性能的问题。比如SQL Server为一个查询编译了一个需要执行书签查找的执行计划，因为用到的非聚集索引没有覆盖到查询字段。

在第8周我们说过，如果你从表获取少量数据，书签查找还是有用的。当你越过临界点时，使用全表/索引扫描将更高效。但是SQL Server如果重用缓存的执行计划，就不会考虑这个选择了——SQL Server只会盲目的重用你的计划——即使性能非常糟糕！我们看看下面的实际执行计划：

 这里SQL Server盲目重用了包含书签查找的被缓存的计划。如你所见，估计行数（estimated number of rows  ）和实际行数（actual number of rows ）完全不同。SQL Server基于假设那个查询只返回1条记录来编译和缓存了计划。但是实际上SQL Server返回了1499条记录。看看执行计划，我们会更清晰，优化器是假设只返回1条记录才执行这个操作的。

这会导致你没有计划稳定性。基于估计行数，你得到书签查找的缓存计划，要不就是如果越过临界点的话是全表/索引扫描。这个是我们在性能调优时经常碰到的性能问题。

如果修正这个问题呢？很简单：通过覆盖非聚集索引来避免书签查找。用这个方法你就得到了计划稳定性，不管你输入的第一个参数是什么值都会有同样的计划和性能。如果你对这个问题感兴趣，可以看下参数嗅探（Parameter Sniffing）（1/2) 和参数嗅探（Parameter Sniffing）（2/2) 。

小结

在这期的性能调优培训里，你看到计划缓存是个双刃剑：一方面，计划缓存非常强大，因为你可以重用已经编译的计划来避免编译成本；另一方面，它非常危险，因为定型的执行计划，你没有计划稳定性，即你不能保证的性能始终如一。

希望这次培训你有所收获，下周我们会详细介绍SQL Server中的重编译。请继续关注！

围观PPT：

 0727_10_计划缓存.rar