參數嗅探 vs 變數 vs 重新編譯 vs 優化未知

所以今天早上我們有一個長時間執行的過程導致問題（30 秒 + 執行時間）。我們決定檢查參數嗅探是否是罪魁禍首。因此，我們重寫了 proc 並將傳入的參數設置為變數，以擊敗參數嗅探。一種嘗試/真實的方法。Bam，查詢時間得到改善（不到 1 秒）。在查看查詢計劃時，可以在原始索引未使用的索引中發現改進。

只是為了驗證我們沒有得到誤報，我們在原始 proc 上執行了 dbcc freeproccache 並重新執行以查看改進的結果是否相同。但是，令我們驚訝的是，原來的 proc 仍然執行緩慢。我們再次嘗試使用 WITH RECOMPILE，仍然很慢（我們嘗試在呼叫 proc 和 proc 本身內部重新編譯）。我們甚至重新啟動了伺服器（顯然是開發盒）。

所以，我的問題是……當我們在一個空的計劃記憶體上獲得相同的慢查詢時，參數嗅探怎麼能被指責……不應該有任何參數來嗅探？

相反，我們是否受到與計劃記憶體無關的表統計資訊的影響。如果是這樣，為什麼將傳入參數設置為變數會有所幫助？

在進一步的測試中，我們還發現在 proc 的內部插入 OPTION (OPTIMIZE FOR UNKNOWN) 確實得到了預期的改進計劃。

所以，你們中的一些人比我聰明，你能提供一些線索來說明幕後發生的事情會產生這種結果嗎？

另一方面，慢速計劃也有理由提前中止，GoodEnoughPlanFound而快速計劃在實際計劃中沒有提前中止原因。

總之

• 根據傳入參數創建變數（1 秒）
• 重新編譯（30+秒）
• dbcc freeproccache (30+ 秒)
• 選項（針對 UKNOWN 進行優化）（1 秒）

更新：

在此處查看慢速執行計劃： https ://www.dropbox.com/s/cmx2lrsea8q8mr6/plan_slow.xml

在此處查看快速執行計劃： https ://www.dropbox.com/s/b28x6a01w7dxsed/plan_fast.xml

注意：出於安全原因，表、模式、對象名稱已更改。

查詢是

SELECT SUM(Amount) AS SummaryTotal
FROM   PDetail WITH(NOLOCK)
WHERE  ClientID = @merchid
      AND PostedDate BETWEEN @datebegin AND @dateend 

該表包含 103,129,000 行。

快速計劃通過 ClientId 使用日期的剩余謂詞進行查找，但需要進行 96 次查找才能檢索Amount. 計劃中的<ParameterList>部分如下。

       <ParameterList>
         <ColumnReference Column="@dateend" 
                          ParameterRuntimeValue="'2013-02-01 23:59:00.000'" />
         <ColumnReference Column="@datebegin" 
                          ParameterRuntimeValue="'2013-01-01 00:00:00.000'" />
         <ColumnReference Column="@merchid" 
                          ParameterRuntimeValue="(78155)" />
       </ParameterList>

慢速計劃按日期查找並查找以評估 ClientId 上的剩余謂詞並檢索數量（估計 1 與實際 7,388,383）。該<ParameterList>部分是

       <ParameterList>
         <ColumnReference Column="@EndDate" 
                          ParameterCompiledValue="'2013-02-01 23:59:00.000'" 
                          ParameterRuntimeValue="'2013-02-01 23:59:00.000'" />
         <ColumnReference Column="@BeginDate" 
                          ParameterCompiledValue="'2013-01-01 00:00:00.000'"               
                          ParameterRuntimeValue="'2013-01-01 00:00:00.000'" />
         <ColumnReference Column="@ClientID" 
                          ParameterCompiledValue="(78155)" 
                          ParameterRuntimeValue="(78155)" />
       </ParameterList>

在第二種情況下，ParameterCompiledValue不為空。SQL Server 成功嗅探查詢中使用的值。

《SQL Server 2005 Practical Troubleshooting》一書有關於使用局部變數的說法

使用局部變數來阻止參數嗅探是一個相當常見的技巧，但是OPTION (RECOMPILE)和OPTION (OPTIMIZE FOR)提示 … 通常是更優雅且風險較小的解決方案

---

＃＃＃筆記

在 SQL Server 2005 中，語句級編譯允許將儲存過程中的單個語句的編譯推遲到第一次執行查詢之前。到那時，局部變數的值將是已知的。從理論上講，SQL Server 可以利用它來嗅探局部變數值，就像它嗅探參數一樣。但是，由於在 SQL Server 7.0 和 SQL Server 2000+ 中通常使用局部變數來阻止參數嗅探，因此在 SQL Server 2005 中未啟用對局部變數的嗅探。它可能會在未來的 SQL Server 版本中啟用，但這是一個不錯的選擇如果您可以選擇，有理由使用本章中概述的其他選項之一。

---

通過快速測試，上述行為在 2008 年和 2012 年仍然是相同的，並且變數不會被嗅探以進行延遲編譯，但只有在使用顯式OPTION RECOMPILE提示時才會被嗅探。

DECLARE @N INT = 0

CREATE TABLE #T ( I INT );

/*Reference to #T means this statement is subject to deferred compile*/
SELECT *
FROM   master..spt_values
WHERE  number = @N
      AND EXISTS(SELECT COUNT(*) FROM #T)

SELECT *
FROM   master..spt_values
WHERE  number = @N
OPTION (RECOMPILE)

DROP TABLE #T 

儘管延遲編譯，變數沒有被嗅探並且估計的行數不准確

所以我假設慢速計劃與查詢的參數化版本有關。

ParameterCompiledValue所有參數都等於，所以這ParameterRuntimeValue不是典型的參數嗅探（計劃是針對一組值編譯的，然後針對另一組值執行）。

問題是為正確的參數值編譯的計劃是不合適的。

您可能會遇到此處和此處描述的升序日期的問題。對於具有 1 億行的表，您需要插入（或以其他方式修改）2000 萬行，SQL Server 才會自動為您更新統計資訊。似乎上次更新它們時，零行與查詢中的日期範圍相匹配，但現在有 700 萬行。

您可以安排更頻繁的統計更新，考慮跟踪標誌2389 - 90或使用OPTIMIZE FOR UKNOWN它只是依靠猜測而不是能夠使用datetime列上目前具有誤導性的統計資訊。

這在 SQL Server 的下一版本（2012 年之後）中可能不需要。相關的Connect 項目包含有趣的響應

Microsoft 於 2012 年 8 月 28 日下午 1:35 發布

我們已經為下一個主要版本進行了基數估計增強，基本上解決了這個問題。一旦我們的預覽出來，請繼續關注細節。埃里克

Benjamin Nevarez 在文章結尾處研究了 2014 年的改進：

初步了解新的 SQL Server 基數估計器。

在這種情況下，新的基數估計器似乎會回退並使用平均密度，而不是給出 1 行估計。

有關 2014 基數估計器和升序關鍵問題的一些額外細節：

SQL Server 2014 中的新功能 - 第 2 部分 - 新基數估計

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