为什么执行sp_recompile有时不帮助参数嗅探？

Question

我们有一个复杂的存储过程，有时会受到参数嗅探的影响。它是一个大型的、“全在一体”的过程，由系统的许多不同部分调用，因此，一个查询计划并不适合所有的用例，这是合理的。

这可以正常工作，除非定期有一个特定的报告从秒到分钟。在过去，一个快速的exec sp_recompile会立即加快它的速度。现在这不管用了。这份报告最终在一两天内“自我修正”，这意味着它要花上几秒钟。

重构存储过程目前不是一个选项，我不想做其他推荐的方法(将参数保存到局部变量，重新编译，为未知优化)，因为这些方法据说还有其他副作用。

所以我有以下问题：

1. 为什么exec sp_recompile不像以前那样加快速度？

1. 如何判断exec sp_recompile是否真的清除了查询计划缓存？在执行之前和之后，应该运行什么？我尝试了一些来自网络的查询，但无法清楚地判断是否有什么变化，所以有一个具体的食谱将是很好的。

1. 是否有理由用不同的名称克隆该过程，并只为这一份报告调用该克隆？目标是让SQL Server仅为报表缓存单独的计划。但我不确定Server是否按过程名称缓存计划，或者它是否缓存存储过程中的各种查询。(如果是后者，那么这种方法就没用了，因为过程的任何克隆都会有相同的查询。)

。

Answer 1

使用几个CTE，特别是复杂的查询(就像加入视图时一样)，可能会导致查询优化器产生最优执行计划的问题。

如果您使用了大量的CTE定义，SQL Server将尝试构建一个单一的单块执行计划，并且您可能会有一个计划编译超时，从而导致使用一个次优计划。

相反，您可以用临时表替换CTEs -使用中间结果通常具有更好的性能，因为每个查询都以一个专用的最优(或至少更好)的计划独立执行。这可以帮助优化器更好地选择联接和索引使用。

如果您可以从两种关键的不同类型的参数中受益，这些参数理想地需要它们自己的最优计划，那么，正如您建议的那样，一个选项将重复每个用例所特有的过程。

您可以通过使用dm_exec_sql_text查询您的过程名来确认这会导致单独的执行计划。

select s.plan_handle, t.text
from sys.dm_exec_query_stats s 
cross apply Sys.dm_exec_sql_text(s.plan_handle)t 
where t.text like '%proc name%'

您将注意到每个过程都有一个不同的plan_handle。