你的位置:首页 > 数据库

[数据库]第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:

 1 SELECT * FROM Sales.SalesOrderHeader 2  3 WHERE CustomerID = 11000 4  5 GO 6  7 SELECT * FROM Sales.SalesOrderHeader 8  9 WHERE CustomerID = 3005210 11 GO12 13 SELECT * FROM Sales.SalesOrderHeader14 15 WHERE CustomerID = 1122316 17 GO

对这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)。

小结

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

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