遇到參數嗅探（Parameter Sniffing）出問題的時候真的會讓人無力，我們為了避免 SQL Injection，所以參數化查詢變成了通則，而正是因為參數化查詢才會有參數嗅探，如果我們是使用 Adhoc 或 Prepared 的方式在下 SQL 語句的話，倒是有一個方法能夠避免因參數嗅探使用到錯誤執行計劃的問題，那就是加入註解。
重現問題
參數嗅探本身不是問題，它一直都在發生，有問題的是它所帶來的副作用，其中一個是當我們的資料分佈嚴重不平均時，會導致錯誤的執行計劃，進而引發查詢效能低落，假設我有一個 Member 資料表，Birthday 欄位有建立非叢集索引，資料表裡面有 100110 筆資料，其中 Birthday 的資料分佈為：
- "2001-01-01" 有 10 筆
- "2002-02-02" 有 100 筆
- "2003-03-03" 有 100000 筆
當我們查詢條件是 Birthday = '2003-03-03' 時，執行計劃跑出來是使用叢集索引掃描。
而當我們查詢條件改為 Birthday = '2002-02-02' 時，執行計劃變成使用索引搜尋加上索引鍵查閱。
也就是說 QO（Query Optimizer）會根據我們下的條件，綜合資料的狀況產生合適的執行計劃，但是我們改用參數化查詢，情況就不一樣了，我們會發現不管我們帶什麼參數進去，執行計劃都一樣。
而且，執行計劃是根據我們第一次執行所帶的參數來決定的。
會這樣是因為參數化查詢的 SQL 語句是固定的，會變的是參數值，所以 SQL Server 發現我們的 SQL 語句沒變，它就把先前已經產生並快取起來的執行計劃拿來用。
加入註解
我們試著分別對這兩個條件下不同的註解看看，會發現參數嗅探造成執行計劃固定的問題消失了，怎麼會這樣？
這個其實我們只是利用了 SQL Server 會針對不同的 SQL 語句產生一個 plan_handle 的特性，我們想像這個 plan_handle 就是執行計劃的 Cache Key，當 SQL Server 在 Plan Cache 中找不到 plan_handle 對應的執行計劃時，就會拿這個 SQL 語句去產生一份執行計劃，然後快取起來。
所以，我們只要依據不同參數，提供不同的 SQL 語句給 SQL Server，SQL Server 就會為這組參數產生一份專屬的執行計劃，那副作用最小的就是在 SQL 語句裡面加入註解了，而且這個註解也不是隨意加的。
執行計劃所佔的記憶體空間其實不算小，我這個的範例所產生的一份執行計劃就至少 40KB 了，因此，如果參數嗅探並沒有為我們帶來太大的影響，那麼我們就應該盡量重覆利用快取的執行計劃，以免大量的執行計劃佔用太多的記憶體，即使要用這種手法來解決參數嗅探所引發的問題，那麼同一組參數所產生的註解應該要一樣。
最後，如果各位朋友也有遇過因為參數嗅探所引發的不同問題，還請不吝留言分享。
參考資料
- [SQL Server] Plan Cache concepts - 認識 Plan Cache
- DBCC FREEPROCCACHE - Remove the specific plan from the cache - 由 Plan Cache 中移除指定的 Execution Plan
- What is the difference between ad hoc and prepared query in sql server plan cache?
