SQL性能:重新编译和删除以及重新应用存储过程

Question

我有一个用于DI报告的存储过程，其中包含使用UNION ALL的62个子查询。最近，性能从不到1分钟上升到8分钟以上，使用SQL，它显示了非常高的CPU和读取。该过程当前已传入设置为局部变量的变量，以防止参数嗅探。

将过程的内容作为SELECT语句和性能运行回到不到一分钟的时间。通过Management中的EXEC调用过程和性能是可怕的，超过8分钟。通过EXEC调用过程，包括使用重新编译命令和性能，都没有提高。我运行了DBCC和DROPCLEANBUFFERS，但仍然没有改进。

最后，我放弃了这个程序，重新应用了它，现在性能又恢复了.

有谁能帮我解释一下，为什么最初的步骤没有纠正程序的性能，但是放弃和重新应用程序却做到了呢？

Answer 1

听起来像是阻塞参数嗅探产生了一个糟糕的计划。当您使用局部变量时，查询优化器使用每个列的密度来得出基数估计，实质上是对平均值进行优化。如果数据分布严重倾斜，则对于某些值，此估计值将显著偏离。这个理论解释了为什么你最初的步骤不起作用。如果阻塞了参数嗅探，使用WITH、重新编译或运行将不会有帮助。每次都会产生同样的计划。因为您说，将过程的内容作为SELECT语句运行使其更快，这使我认为您实际上需要参数嗅探。但是，如果编译时间是可以接受的，您也需要尝试使用WITH重新编译，否则就有可能因为一个错误的计划而被困在一个错误的嗅探值之上。