SQL Server实际执行计划COST"欺骗"案例

有个系统，昨天Support人员发布了相关升级脚本后，今天发现系统中有个功能不能正常使用了，直接报超时了（Timeout expired）的错误。定位到相关相关存储过程后，然后在优化分析的过程中，又遇到了执行计划COST “欺骗”我们的这种情况，其实在我这篇博客”SQL SERVER中用户定义标量函数（scalar user defined function）的性能问题“有提及这个问题，但是很多时候，我们优化SQL的时候，会习惯去查看实际执行计划COST所占的开销比例,从而判断性能开销最大的SQL语句。当然大多数时候，这也是正确的。我们先来看看这个案例吧，如下所示，这个存储过程的部分实际执行计划如下（实际执行计划实在太长，无法全部展现）：

 

 

 

 

我们将实际执行计划保存为sqlplan类型的文件（Execution Plan Files），然后用Plan Explorer展现出来，如下所示，Est Cost% 和 Est CPU  Cost% 显示第一个SQL语句是整个存储过程里面开销消耗最大的SQL语句。然后去测试验证，发现这个SQL不是开销最大的SQL，也就是说执行计划欺骗了我们，实际上，下面Est Cost %为13.3的SQL才是性能开销最大的SQL

 

 

 

 

 

从实际执行计划中找到elapsed time最长的SQL，这个SQL才是真正影响性能的SQL语句，然后查看这个SQL语句，发现其查询条件（WHERE）使用了自定义标量函数（因为修改业务逻辑，查询条件添加了自定义函数过滤数据），然而这个从实际执行计划去看也是看不出问题的，因为这个自定义标量函数哪怕调用了几十万次，它的开销代价在实际执行计划中并没有呈现出来。具体原因截取“SQL SERVER中用户定义标量函数（scalar user defined function）的性能问题“中的一段翻译如下：

 

翻译：

但是需要再次注意，执行计划在欺骗你，首先，它意味着只调用了UDF一次，其实不是这样。其次，从成本（Cost）来看，你可能会认为0%是向下舍入影响，因为单次执行函数的开销如此之小，以至于执行100,000次的成本也很小。但如果你检查执行计划的功能迭代器的属性，你会发现所有的操作代价和子树代价实际的估计为0，这是一个最糟糕的谎言。因为它可能不只是为了欺骗我们，而是SQL SERVER为了欺骗它自己。实际上是查询优化器认为调用函数的成本为0，因此它生成的所有执行计划都是基于调用UDF是免费的。其结果是即使调用标量UDF的代价非常昂贵，查询优化器也不会考虑优化它。

 

 

其实又单独总结一下这个问题，是因为人们或多或少受习惯性思维的影响，哪怕我之前多次遇到这种案例，但是在调优过程中，我还是会习惯性按照实际执行计划的COST比例去定位性能开销大的SQL语句，直到我通过验证推翻了这个判断，然后通过elapsed time最长的SQL语句才定位到开销最大的SQL。所以在调优、优化过程中，一定要多方位着手，反复推敲验证，不能被经验主义牵着鼻子走！