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

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

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

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

SQL Server提供給你的第一個選項是執(zhí)行計劃的重編譯。它提供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

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

1 EXEC dbo.RetrieveDataR @Col2Value = 1 -- int2 EXEC dbo.RetrieveDataR @Col2Value = 2 -- int

如果你的參數(shù)嗅探問題只出現(xiàn)在一個特定的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語句在存儲過程執(zhí)行的時候都會重編譯。第1個語句在執(zhí)行初始時編譯好，并生成計劃緩存做后續(xù)重用。在你不想修改數(shù)據(jù)庫的索引時，這個方法是處理參數(shù)嗅探的推薦方法。

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

除了存儲過程或SQL語句的重編譯查詢提示，SQL Server也提供OPTIMIZE FOR的查詢提示。用這個查詢提示你可以告訴查詢優(yōu)化器哪個參數(shù)值下，對執(zhí)行計劃執(zhí)行優(yōu)化，我們看下面的例子：

 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語句的執(zhí)行計劃在參數(shù)@Col2Value值為1的時候需要進行優(yōu)化。不管你提供給這個參數(shù)的任何值，你都獲得為值1優(yōu)化的編譯計劃。用這個方法你已經(jīng)對SQL Server放大招了，因為查詢優(yōu)化器沒別的選項——它必須為參數(shù)值1生成優(yōu)化的的執(zhí)行計劃。當(dāng)你知道查詢計劃需要為指定參數(shù)進行優(yōu)化時，可以使用這個方法讓SQL Server對此參數(shù)的執(zhí)行計劃進行優(yōu)化。在你重啟SQL Server或執(zhí)行群集故障轉(zhuǎn)移時，就可以預(yù)知你的執(zhí)行計劃。

為了進一步保障這個選項的有效性，你就要熟悉你的數(shù)據(jù)分布情況，還有什么時候數(shù)據(jù)分布情況會改變。如果數(shù)據(jù)分布情況已經(jīng)改變，你就要修改查詢提示，看看是否仍然合適。你不能完全相信查詢優(yōu)化器，因為你已經(jīng)用OPTIMIZE FOR查詢提示重置查詢優(yōu)化器的選擇。要記住這個。另外在提供OPTIMIZE FOR查詢提示的同時，SQL Server也提供OPTIMIZE FOR UNKNOWN查詢提示。如果你決定使用OPTIMIZE FOR UNKNOWN查詢提示，查詢優(yōu)化器就使用表統(tǒng)計信息里的密度來做參數(shù)預(yù)估。如果邏輯讀超過了臨界點，還是會使用表/索引掃描……

在這個文章里我向你展示在SQL Server里處理參數(shù)嗅探問題的不同方式。其中造成這個問題的最常見原因是糟糕的索引設(shè)計，造成參數(shù)值傳入后優(yōu)化器在執(zhí)行計劃里選擇了書簽查找。如果這樣的執(zhí)行計劃被緩存重用的話，你的I/O成本就會爆表。在生成環(huán)境中，我就看到因為這個原因就造成100GB的邏輯讀。在SQL語句上加一個簡單的RECOMPILE查詢提示就可以解決這個問題，查詢只會增加少量的邏輯讀。

如果你不能修改數(shù)據(jù)庫索引設(shè)計，你可以在存儲過程或SQL語句上使用RECOMPILE查詢提示。作為副作用編譯的計劃就不會緩存。除此外的查詢提示，SQL Server還提供OPTIMIZE FOR和OPTIMIZE FOR UNKNOWN的查詢提示。在你使用這些查詢提示時，你要對你的數(shù)據(jù)和數(shù)據(jù)分布情況非常熟悉，因為你在重置優(yōu)化器。請慎重使用！Be always aware of this fact!