如果SQL query中有參數，SQL Server 會創建一個參數嗅探進程以提高執行性能。該計劃通常是最好的並被保存以重覆利用。只是偶爾，不會選擇最優的執行計劃而影響執行效率。 SQL Server嘗試通過創建編譯執行計劃來優化你的存儲過程的執行。通常是在第一次執行存儲過程時候會生成並緩存查詢執行 ...
如果SQL query中有參數，SQL Server 會創建一個參數嗅探進程以提高執行性能。該計劃通常是最好的並被保存以重覆利用。只是偶爾，不會選擇最優的執行計劃而影響執行效率。
SQL Server嘗試通過創建編譯執行計劃來優化你的存儲過程的執行。通常是在第一次執行存儲過程時候會生成並緩存查詢執行計劃。當SQL Server資料庫引擎編譯存儲過程中偵測到有參數值傳遞進來的時候，會創建基於這些參數的執行計劃。這種在編譯存儲過程中偵測參數值的方法，通常被稱為“參數探測”。有時參數探測會產生效率低下的執行計劃;特別是當一個存儲過程調用與具有不同的基數的參數值。
什麼是參數探測
探測一詞就顯示出了更多的不可靠性，有時候會產生好的結果就不可避免的產生一些壞的結果。參數探測是在SQL SERVER通過第一次執行時調用的參數創建的最優的執行計劃。 這個第一次是指不管你執行或者是重新編譯因為在緩存中沒有一個現成的執行計劃存在。以後使用相同的參數調用同一個存儲過程的時候同樣會得到一個最佳的執行方案。但是使用不同的參數的時候可能得不到最佳的方案，就是壞的結果。
並不是所有的執行計劃是平等的，執行計劃會按照要做什麼進行一些必要的優化。SQL SERVER再去選擇並確定最優的執行策略。它著眼於做什麼樣的查詢，使用參數值來看看統計數據，做了那些計算，最終決定通過哪些步驟來解決查詢。這是如何創建一個執行計劃的比較簡單的解釋。對我們來說，重要的一點是，SQL Server通過這些參數用來確定如何處理查詢。一組參數的最優執行計劃可能是一個索引掃描操作，而另一組參數可能使用索引查找能更好地解決。
參數探測影響示例
假設我們有一個基於國家查詢運行銷售數據的存儲過程：
EXEC rpt_Sales @Country ='China' - SQL Server構建針對大量銷售額的國家而優化的執行計劃，並且運行時間大約為750毫秒。
EXEC rpt_Sales @Country ='Monaco' - 它重用了中國的大數據緩存執行計劃。這對於小國來說不是很好，但也沒有那麼糟糕因為只返回少量數據，所以它仍然運行在500毫秒。
現在我們重新啟動SQL Server，有人首先查詢Monaco：
EXEC rpt_Sales @Country ='Monaco' - SQL Server構建了一個針對微小數據量的國家而優化的執行計劃，它運行時間只有50毫秒 - 比摩納哥重用中國計劃時要好很多！
EXEC rpt_Sales @Country ='China' - 它重用了Monaco的緩存計劃來處理小數據。 它需要30秒，如果很多人同時運行這個存儲過程，我們的伺服器要開始崩潰了！
如何臨時修複參數探測問題
1． 重啟伺服器 – 新手最先想到的解決方案，遇到問題先重啟。重啟伺服器後會清除所有的緩存。重啟完後，有人首先使用'China'去運行了rpt_Sales, 它將建立一個好的執行計劃。一些初級DBA會覺得這個問題已經被修複。
2． 重啟SQL Server 實例 – 隨著工作經驗的增長，他們意識到不能隨意的重啟伺服器。發現重啟實例也可以達到重啟伺服器的效果。。
3． 運行DBCC FREEPROCCACHE - 此命令會從緩存中清除所有執行計劃，但不清除SQL Server的其他緩存和統計信息。相比重啟來說，是個比較好的方案，因為至少可以確保資料庫線上的情況下完成。
4． 重建索引 – 這其實是個意外，當SQL Server重建一個表的索引時會同時更新索引的統計信息。這也會修複了參數探測問題，因為當SQL Server意識到傳入查詢使用的對象的統計信息 (statistics)更新時，它將為該查詢構建一個新的執行計劃。
5．更新統計信息 (statistics) - 當人們學習到上述額外發現，他們意識到他們可以通過只更新統計信息數據，這是一個比重建索引容易得多的操作。
6．對一個表或存儲過程運行sp_recompile - 此系統存儲過程接受表或存儲過程名稱作為參數，並將所有相關的執行計劃標記為在下次運行時強制重新編譯。
7．清除特定的執行計劃緩存 - 為單個查詢運行DBCC FREEPROCCACHE, 只清除特定(比如rpt_Sales)的執行計劃緩存。
禁用參數探測
既然參數探測會帶來不確定的因素，我們可以通過使用本地變數來禁止參數探測。
比如：
create procedure rpt_Sales (@Country varchar(20))
as
begin
declare @LocCountry varchar(20)
set @LocCountry = @CustID
select * from orders
where Country = @LocCountry
end
歸納總結
參數探測（Parameter
Sniffing）可以在存儲過程級別上啟用或禁用；
如果檢索的數據列基本上平均分佈，我們不必使用本地變數（禁用Parameter
Sniffing）；例如，查詢主鍵列或唯一鍵列（Unique
Key）；
如果檢索的數據列分佈很大，則可以使用本地變數，禁用參數探測（Parameter
Sniffing）；
