在參數嗅探（Parameter Sniffing）（1/2)裡，我介紹了SQL Server裡參數嗅探的基本概念和背後的問題。如你所見，當緩存的計劃被SQL Server盲目重用時，會帶來嚴重的性能問題。今天我會向你展示下如何處理這個問題，即使用不同的技術克服它。
索引（Index）
上次我們讨論造成參數嗅探問題的根源是：在執行計劃裡，SQL 語句有時會産生書簽查找，有時會産生表/聚集索引掃描。如果你能在資料庫裡修改索引，解決這個問題的最簡單方法就是提供查詢列對應的覆寫非聚集索引。這裡我們就要包含書簽查找的需要列，在非聚集索引的葉子層。這樣做後，就可以獲得計劃穩定性：不管提供的輸入任何參數，查詢優化器都可以編譯同樣的執行計劃——這裡就是都會用到索引查找（非聚集索引）運算符。
1 DROP INDEX idx_Test ON Table1
2 CREATE NONCLUSTERED INDEX idx_Test ON Table1(Column2) INCLUDE(Column1)
3
4 SELECT * FROM dbo.Table1 WHERE Column2=1
5 SELECT * FROM dbo.Table1 WHERE Column2=2 
如果你不能修改你的索引設計，可以嘗試下面的方法：
重編譯（Recompilation）
SQL Server提供給你的第一個選項是執行計劃的重編譯。它提供2個不同選項給你使用：
- 全部重編譯，整個存儲過程
- 有問題的SQL語句重編譯，即所謂的語句級别的重編譯（從SQL Server 2005起可用）
我們通過執行個體詳細講解下這2個選項。下面的語句會對整個存儲過程進行重編譯：
1 -- Create a new stored procedure for data retrieval
2 CREATE PROCEDURE RetrieveDataR
3 (
4 @Col2Value INT
5 )
6 WITH RECOMPILE
7 AS
8 SELECT * FROM Table1
9 WHERE Column2 = @Col2Value
10 GO 當你執行這樣的存儲過程時，查詢優化器在每次執行前都會重新編譯存儲過程。是以你得到的執行計劃都是基于目前輸入的參數值。作為重編譯的副作用，你的執行計劃不會被緩存，對于一個每次都重編譯的執行計劃進行緩存是沒有意義的。當你有一個大的複雜的存儲過程在存儲過程級别使用RECOMPILE選項，這樣做就沒太大意義，因為你的整個存儲每次都重編譯，而存儲過程就是為了編譯好進行重用，進而提高執行效率。
1 EXEC dbo.RetrieveDataR @Col2Value = 1 -- int
2 EXEC dbo.RetrieveDataR @Col2Value = 2 -- int 
如果你的參數嗅探問題隻出現在一個特定的SQL語句。那就沒有必要對整個存儲過程進行重編譯了。是以從SQL Server2005開始，提供稱為語句級别的重編譯（Statement Level Recompilation） 。你可以對需要重編譯的SQL語句加上RECOMPILE查詢提示而不是整個存儲過程。我們來看下下面的代碼：
1 -- Create a new stored procedure for data retrieval
2 CREATE PROCEDURE RetrieveDataR2
3 (
4 @Col2Value INT
5 )
6 AS
7 SELECT * FROM Table1
8 WHERE Column2 = @Col2Value
9
10 SELECT * FROM Table1
11 WHERE Column2 = @Col2Value
12 OPTION (RECOMPILE)
13 GO 上述例子裡的第2個SQL語句在存儲過程執行的時候都會重編譯。第1個語句在執行初始時編譯好，并生成計劃緩存做後續重用。在你不想修改資料庫的索引時，這個方法是處理參數嗅探的推薦方法。
1 EXEC dbo.RetrieveDataR2 @Col2Value = 2 -- int OPTIMIZE FOR
除了存儲過程或SQL語句的重編譯查詢提示，SQL Server也提供OPTIMIZE FOR的查詢提示。用這個查詢提示你可以告訴查詢優化器哪個參數值下，對執行計劃執行優化，我們看下面的例子：
1 -- Create a new stored procedure for data retrieval
2 CREATE PROCEDURE RetrieveDataOF
3 (
4 @Col2Value INT
5 )
6 AS
7 SELECT * FROM Table1
8 WHERE Column2 = @Col2Value
9 OPTION (OPTIMIZE FOR (@Col2Value = 1))
10 GO 從存儲過程的定義中你可以看到，SQL語句的執行計劃在參數@Col2Value值為1的時候需要進行優化。不管你提供給這個參數的任何值，你都獲得為值1優化的編譯計劃。用這個方法你已經對SQL Server放大招了，因為查詢優化器沒别的選項——它必須為參數值1生成優化的的執行計劃。當你知道查詢計劃需要為指定參數進行優化時，可以使用這個方法讓SQL Server對此參數的執行計劃進行優化。在你重新開機SQL Server或執行群集故障轉移時，就可以預知你的執行計劃。
為了進一步保障這個選項的有效性，你就要熟悉你的資料分布情況，還有什麼時候資料分布情況會改變。如果資料分布情況已經改變，你就要修改查詢提示，看看是否仍然合适。你不能完全相信查詢優化器，因為你已經用OPTIMIZE FOR查詢提示重置查詢優化器的選擇。要記住這個。另外在提供OPTIMIZE FOR查詢提示的同時，SQL Server也提供OPTIMIZE FOR UNKNOWN查詢提示。如果你決定使用OPTIMIZE FOR UNKNOWN查詢提示，查詢優化器就使用表統計資訊裡的密度來做參數預估。如果邏輯讀超過了臨界點，還是會使用表/索引掃描……
小結
在這個文章裡我向你展示在SQL Server裡處理參數嗅探問題的不同方式。其中造成這個問題的最常見原因是糟糕的索引設計，造成參數值傳入後優化器在執行計劃裡選擇了書簽查找。如果這樣的執行計劃被緩存重用的話，你的I/O成本就會爆表。在生成環境中，我就看到因為這個原因就造成100GB的邏輯讀。在SQL語句上加一個簡單的RECOMPILE查詢提示就可以解決這個問題，查詢隻會增加少量的邏輯讀。
如果你不能修改資料庫索引設計，你可以在存儲過程或SQL語句上使用RECOMPILE查詢提示。作為副作用編譯的計劃就不會緩存。除此外的查詢提示，SQL Server還提供OPTIMIZE FOR和OPTIMIZE FOR UNKNOWN的查詢提示。在你使用這些查詢提示時，你要對你的資料和資料分布情況非常熟悉，因為你在重置優化器。請慎重使用！Be always aware of this fact!
參考文章：
https://www.sqlpassion.at/archive/2014/10/27/parameter-sniffing-part-2/
注：此文章為
WoodyTu學習MS SQL技術，收集整理相關文檔撰寫，歡迎轉載，請在文章頁面明顯位置給出此文連結！
若您覺得這篇文章還不錯請點選下右下角的推薦，有了您的支援才能激發作者更大的寫作熱情，非常感謝！