如果參數儲存在局部變數中，則執行計劃更好

我有兩個儲存過程。這個速度非常快（~ 2 秒）

CREATE PROCEDURE [schema].[Test_fast]
  @week date
AS
BEGIN

   declare @myweek date = @week

   select distinct serial 
   from [schema].[tEventlog]         as e
   join [schema].tEventlogSourceName as s on s.ID = e.FKSourceName
   where s.SourceName = 'source_name'
       and (e.EventCode = 1 or e.EventCode = 9)
       and cast(@myweek as datetime2(3)) <= [Date] 
       and [Date] < dateadd(day, 7, cast(@myweek as datetime2(3)))    
END

而這個執行緩慢（~ 2 小時）：

create PROCEDURE [schema].[Test_slow]
  @week date
AS
BEGIN

   select distinct serial 
   from [schema].[tEventlog]         as e
   join [schema].tEventlogSourceName as s on s.ID = e.FKSourceName
   where s.SourceName = 'source_name'
       and (e.EventCode = 1 or e.EventCode = 9)
       and cast(@week as datetime2(3)) <= [Date] 
       and [Date] < dateadd(day, 7, cast(@week as datetime2(3)))
END

唯一真正的區別是行（使用局部變數@myweek）：

declare @myweek date = @week

以下是執行計劃。第一個計劃是從

$$ schema $$.$$ Test_fast $$第二個來自$$ schema $$.$$ Test_slow $$:

我的問題是：為什麼當我獲取參數並將其儲存在局部變數中，然後使用此局部變數時，SQL Server 2012 會獲得更好的執行計劃（更快）。統計數據或索引是否有問題？（我也想知道為什麼第二個執行計劃沒有使用任何類型的並行執行）。

更新：

我給 2 個 SP 相同的參數並在同一時間（接近 2 秒的時間差異）啟動它們，這不是自動更新此數據庫中的統計資訊。

例子：

EXEC    [schema].[Test_fast]
       @week = '2016-02-08'

EXEC    [schema].[Test_slow]
       @week = '2016-02-08'

以下是執行計劃：

https://gist.github.com/anonymous/6e404f896d9613c2061a#file-sp_execution_plan-sqlplan

索引的額外更新也沒有效果。

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

從提供的執行計劃來看，這正是您的情況。

使用局部變數時，無法嗅探變數中的值：

注意空白的“編譯值”。查詢優化器根據Date列中值的平均分佈（或可能是一個完整的猜測）估計更多的行數，從而導致並行計劃。

直接使用儲存過程參數時，會嗅探@week的值：

優化器使用值“2016-02-08”估計將匹配查詢謂詞的行數，插入：

and cast(@week as datetime2(3)) <= [Date] 
and [Date] < dateadd(day, 7, cast(@week as datetime2(3)))

它帶有一行的估計值，導致選擇帶有鍵查找的串列計劃。上面的謂詞對於基數估計不是很友好，所以 1-row 估計可能不是很準確。您可以嘗試啟用跟踪標誌 4199，但不能保證估計會有所改善。

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

參數嗅探、嵌入和RECOMPILE選項

通常，儲存過程的初始執行也可能發生在 @week 的選擇性值非常高的情況下，預期的行數很少。另一個可能導致問題的原因是在初始呼叫中使用了最近的 @week 值，此時統計資訊尚未更新以涵蓋此值範圍（這是Ascending Key Problem）。

@week 非常有選擇性的嗅探值可能會導致查詢優化器選擇具有索引查找和鍵查找的非並行計劃。該計劃將被記憶體以供將來使用不同參數值的過程執行時重用。如果稍後執行（@week 的值不同）選擇比最初更多的行，則該計劃可能會執行得很差，因為 seek + key 查找不再是一個好的策略。

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