title: sql server - 最佳實踐 - 參數嗅探問題
mssql server參數嗅探既是一個涉及知識面非常廣泛，又是一個比較難于解決的課題，即使對于資料庫老手也是一個比較頭痛的問題。這篇文章從參數嗅探是什麼，如何産生，表象是什麼，會帶來哪些問題，如何解決這五個方面來探讨參數嗅探的來龍去脈，期望能夠将sql server參數嗅探問題理清楚，道明白。
當sql server第一次執查詢語句或存儲過程（或者查詢語句與存儲過程被強制重新編譯）的時候，sql server會有一個程序來評估傳入的參數，并根據傳入的參數生成對應的執行計劃緩存，然後參數的值會伴随查詢語句或存儲過程執行計劃一并儲存在執行計劃緩存裡。這個評估的過程就叫着參數嗅探。
sql server對查詢語句編譯和緩存機制是sql語句執行過程中非常重要的環節，也是sqlos記憶體管理非常重要的一環。理由是sql server對查詢語句編譯過程是非常消耗系統性能，代價昂貴的。因為它需要從成百上千條執行路徑中選擇一條最優的執行計劃方案。是以，查詢語句可以重用執行計劃的緩存，避免重複編譯，以此來節約系統開銷。這種編譯查詢語句，選擇最優執行方案，緩存執行計劃的機制就是參數嗅探問題産生的理論基礎。
以上是比較枯燥的理論解釋，這裡我們來看看兩個實際的例子。在此，我們以adventureworks2008r2資料庫中的sales.salesorderdetail表做為我們測試的資料源，我們挑選其中三個典型的産品，productid分别為897，945和870，分别對應的訂單總數為2，257和4688。
挑選的方法如下：
得到如下結果：
接下來，我們看三個非常相似的查詢語句（僅傳入的參數值不同）的執行計劃有什麼差異。
三個查詢語句：
分别的執行計劃：
從這個執行計劃對比來看，productid為945和897的兩條語句執行計劃一緻，因為滿足條件的記錄數非常少，分别為257條和2條，是以sql server均選擇走最優執行計劃index seek + key lookup。但是與productid為870的查詢語句執行計劃完全不同，這條語句sql server選擇走的是clustered index scan，幾乎等價于table scan的性能消耗。這是因為，sql server認為滿足條件productid = 870的記錄數太多，達到了4688條記錄，與其走index seek + key lookup，還不如走clustered index scan順序io的效率高。從這裡可以看出，sql server會因為傳入參數值的不同而選擇走不同的執行計劃，執行效率也大不相同。确切的說，這個就是屬于查詢語句的參數嗅探問題範疇。
上一小節，我們看了查詢語句的參數嗅探表象，這一小節我們來看看存儲過程參數嗅探的表象又是如何的呢？
首先，我們建立如下存儲過程:
接下來，我們執行兩次這個存儲過程，傳入不同的參數：
從這個執行計劃來看，productid為870和945走的相同的執行計劃clustered index scan，這個和上一小節得到的結果是不一樣的。上一節中productid = 945的查詢語句執行計劃走的是index seek + key lookup。
當我們選擇第二個執行計劃的clustered index scan的時候，我們觀察properties中的estimated number of rows，這裡顯示的是4668，但實際上正确得行數應該是257。如下如所示：
這到底是為什麼呢？從另外一個角度來講，這個不正确的統計估值甚至會導緻sql server走到一個不是最優的執行計劃上來（根據上一小節，productid = 945的最優執行計劃其實是index seek + key lookup）。
答案其實就是存儲過程的參數嗅探問題。這是因為，我們在首次執行這個存儲過程的時候，傳入的參數productid = 870對應的訂單總數為4668，sql server在編譯，緩存執行計劃的時候，連同這個值一起記錄到執行計劃緩存中了。進而影響到存儲過程的第二次及以後的執行計劃方案，進而影響到存儲過程的執行效率。
我們可以通過如下方法來檢視執行計劃中傳入參數的值，右鍵 => show execution plan xml => 搜尋 parametercompiledvalue
在此例中，我們很清楚的發現傳入參數值是870，同時也很清楚得看到了參數嗅探對于執行計劃的影響：
至此，我們分别從查詢語句和存儲過程兩個友善看到了參數嗅探的表象。
從參數嗅探的表象這一章節，我們可以對此參數嗅探的問題窺探一二。但是，參數嗅探可能會導緻哪些常見的問題呢？根據我們的經驗，如果你遭遇了mssql server以下奇怪問題，你可能就遇到參數嗅探這個“大魔頭”了。
某些傳入參數導緻存儲過程執行非常緩慢，但是alter procdure（所有代碼沒做任何改動）後，性能恢複正常。這個場景是我們之前經常遇到的，原因是當你alter procedure後，mssql server會主動清除對應的存儲過程執行計劃緩存，然後再次執行該存儲過程的時候，系統會重新編譯并緩存該存儲過程執行計劃。
這個聽起來非常奇怪吧，當我們執行相同的存儲過程，傳入相同的參數值，但是執行效率時快時慢，請注意下面例子中的注釋部分。
現在我們清空了執行計劃緩存，為了友善，我直接清除所有的執行計劃緩存。
再次執行存儲過程，這次我們交換了執行順序，先執行productid 945，然後執行productid 870。
從這兩個批次執行的時間對比來看，productid 945和870執行時間有比較大的差異，特别是productid = 870。這種相同的存儲過程，相同的傳入參數，執行時快時慢的問題，也是由于參數嗅探導緻的。
注意：這裡隻是為了描述這種現象，由于表資料量本來不大的原因，可能實際上執行時間可能沒有那麼大的差異。
某一個查詢語句，放在存儲過程中執行和拿出來單獨執行，時間消耗差異大，一般情況是拿出來單獨執行的語句很快，放到存儲過程中執行很慢。這個情況也是我們在産品環境常見的一種典型參數嗅探導緻的問題。
上一節，我們探讨了參數嗅探可能會導緻的問題。當發現這些問題的時候，我們來看看兩類人的不同解決方法。請允許我将這兩類人分别命名為菜鳥和老鳥，沒有任何歧視，隻是一個名字代号而已。
菜鳥的理論很簡單粗暴，既然參數嗅探是因為查詢語句或者存儲過程的執行計劃緩存導緻，那麼我隻需要清空記憶體就可以解決這個問題了嘛。嗯，來看看菜鳥很傻很天真的做法吧。
方法一：重新開機windows os。果然很黃很暴力，重新開機windows作業系統，徹底清空windows所有記憶體内容。
方法二：重新開機sql server service。稍微溫柔一點點啦，重新開機sql server service，徹底清空sql server占用的所有記憶體，當然執行計劃緩存也被清空了。
方法三：dbcc指令清空sql server執行計劃緩存。又溫柔了不少吧，徹底清空了sql server所有的執行計劃緩存，包含有問題的和沒有問題的緩存。
當菜鳥還在為自己的解決方法解決了參數嗅探問題而沾沾自喜的時候，老鳥的思維已經走得很遠很遠了，老鳥就是老鳥，是菜鳥所望塵莫及的。老鳥的思維邏輯其實也很簡單，既然是某個或者某些查詢語句或存儲過程的執行計劃緩存有問題，那麼，我們隻需要重新編譯緩存這些害群之馬就好了。
方法一：建立存儲過程使用with recompile
再重新執行兩次存儲過程，傳入不同的參數值，我們可以看到均走到最優的執行計劃上來了，說明參數嗅探的問題已經解決。這個方法帶來的一個問題就是每次執行這個存儲過程系統都會重新編譯，無法使用執行計劃緩存。但是相對來說，重新編譯的系統開銷要遠遠小于參數嗅探導緻的系統性能消耗，是以，兩害取其輕。
方法二：查詢語句使用query hits
如果我們知道productid對應的訂單總數分布，認為productid = 945為最好的執行計劃，那麼我們可以強制sql server按照參數輸入945來執行存儲過程，我們可以添加query hits來實作。這種方法的難點在于對表中資料分布有着精細的認識，可操作性不強，因為表中資料分布是随時在改變的。
方法三：dbcc清除特定語句或存儲過程緩存
當清除執行計劃緩存後，sql server再次執行會重新編譯對應語句或者存儲過程，以獲得最好的執行計劃。在此以清除特定存儲過程執行計劃緩存為例。
方法四：更新表對象統計資訊
表統計資訊過時導緻執行計劃評估不準确，進而影響查詢語句執行效率。這個也是導緻參數嗅探問題另一個重要原因。這種情況，我們隻需要手動更新表統計資訊。這個解決方法的難點在于找到有問題的查詢語句和對應有問題的表。統計資訊更新方法如下，如果發現statsupdated時間太過久遠就應該是被懷疑的對象：
方法五：重整表對象索引
另外一個導緻執行計劃評估不準确的重要原因是索引碎片過高（超過30%），這個也會導緻參數嗅探問題的重要原因。這種情況我們需要手動重整索引碎片，方法如下：
方法六：建立缺失的索引
還有一個重要的導緻執行計劃評估不準确的因素是表缺失索引，這個也是會導緻參數嗅探的問題。查找缺失索引的方法如下：
方法七：使用本地變量
這是一個非常奇怪的解決方法，使用這種方法的原因是，對于本地變量sql server使用統計密度來代替統計直方圖，它會認為所有的本地變量均擁有相同的統計密度，即對應于相同的記錄數。這樣可以避免因為資料分布不均勻導緻的參數嗅探問題。
至此結束，本節分享了菜鳥和老鳥關于參數嗅探問題的解決方法，我相信大家應該可以輕松的做出正确選擇适合自己的解決方法。
以上所有代碼的測試環境是在mssql server 2008r2 enterprise中完成。