為什麼 RECOMPILE 不能修復參數嗅探，但局部變數可以？

一位開發人員問我為什麼他的程序沒有執行，但查詢沒問題。

當我執行查詢時，它很快。但是執行該過程，它一直在執行。

我所做的是在查詢末尾添加：

OPTION(RECOMPILE)

它沒有幫助。

過程是這樣的（慢的）：

CREATE procedure [dbo].[pr_ProcedureName]  
   @CodOnda int  
AS
BEGIN
 set nocount on  
 ...

然後我這樣做了：

CREATE procedure [dbo].[pr_ProcedureName]  
   @CodOnda int  
AS
BEGIN
  set nocount on  

  declare @localCodOnda int
  set @localCodOnda = @CodOnda
  ...

還有 BAM！局部變數。這個過程比以往任何時候都快。我知道這是關於參數嗅探的事情。但是為什麼重新編譯沒有幫助？

我真的很想擁有舊的查詢計劃，但我沒有。

問題不在於鎖定或阻塞。我正在跟踪sp_WhoIsActive以及我在此處的一些答案中找到的一些查詢（感謝 Aaron Bertrand 和 Brent Ozar）。我正在使用 SQL Server 2008 R2。

但是為什麼重新編譯沒有幫助？

使用OPTION (RECOMPILE)不是魔術。它確實通常會產生性能更好的執行計劃（以每次完全編譯為代價），因為每次都會針對當時任何參數的特定值編譯查詢，並且還啟用參數嵌入，這可能會產生進一步的好處.

OPTION (RECOMPILE)確定對您的特定情況沒有幫助的具體原因需要您無法提供的詳細資訊。

局部變數。這個過程比以往任何時候都快。

使用局部變數可以防止對參數值的嗅探，因此查詢是根據平均分佈統計資訊編譯的。這是之前某些類型的參數敏感性問題的解決方法，OPTION (OPTIMIZE FOR UNKNOWN)並且跟踪標誌 4136變得可用。

欲了解更多詳情，請參閱：

參數嗅探、嵌入和RECOMPILE選項

引用自：https://dba.stackexchange.com/questions/107807