在參數嗅探（Parameter Sniffing）（1/2)里，我介紹了SQL Server里參數嗅探的基本概念和背后的問題。如你所見，當緩存的計劃被SQL Server盲目重用時，會帶來嚴重的性能問題。今天我會向你展示下如何處理這個問題，即使用不同的技術克服它。

上次我們討論造成參數嗅探問題的根源是：在執行計劃里，SQL 語句有時會產生書簽查找，有時會產生表/聚集索引掃描。如果你能在數據庫里修改索引，解決這個問題的最簡單方法就是提供查詢列對應的覆蓋非聚集索引。這里我們就要包含書簽查找的需要列，在非聚集索引的葉子層。這樣做后，就可以獲得計劃穩定性：不管提供的輸入任何參數，查詢優化器都可以編譯同樣的執行計劃——這里就是都會用到索引查找（非聚集索引）運算符。

1 DROP  INDEX idx_Test ON Table12 CREATE NONCLUSTERED INDEX idx_Test ON Table1(Column2) INCLUDE(Column1)3 4 SELECT * FROM dbo.Table1 WHERE Column2=15 SELECT * FROM dbo.Table1 WHERE Column2=2

如果你不能修改你的索引設計，可以嘗試下面的方法：

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 = @Col2Value10 GO

當你執行這樣的存儲過程時，查詢優化器在每次執行前都會重新編譯存儲過程。因此你得到的執行計劃都是基于目前輸入的參數值。作為重編譯的副作用，你的執行計劃不會被緩存，對于一個每次都重編譯的執行計劃進行緩存是沒有意義的。當你有一個大的復雜的存儲過程在存儲過程級別使用RECOMPILE選項，這樣做就沒太大意義，因為你的整個存儲每次都重編譯，而存儲過程就是為了編譯好進行重用，從而提高執行效率。

1 EXEC dbo.RetrieveDataR @Col2Value = 1 -- int2 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 Table111     WHERE Column2 = @Col2Value12         OPTION (RECOMPILE)13 GO

上述例子里的第2個SQL語句在存儲過程執行的時候都會重編譯。第1個語句在執行初始時編譯好，并生成計劃緩存做后續重用。在你不想修改數據庫的索引時，這個方法是處理參數嗅探的推薦方法。

1 EXEC dbo.RetrieveDataR2 @Col2Value = 2 -- int

除了存儲過程或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!