[廚餘回收] 解決因參數嗅探(Parameter Sniffing)導致使用到錯誤執行計劃的方法之一 - 加入註解。

遇到參數嗅探(Parameter Sniffing)出問題的時候真的會讓人無力,我們為了避免 SQL Injection,所以參數化查詢變成了通則,而正是因為參數化查詢才會有參數嗅探,如果我們是使用 Adhoc 或 Prepared 的方式在下 SQL 語句的話,倒是有一個方法能夠避免因參數嗅探使用到錯誤執行計劃的問題,那就是加入註解

重現問題

參數嗅探本身不是問題,它一直都在發生,有問題的是它所帶來的副作用,其中一個是當我們的資料分佈嚴重不平均時,會導致錯誤的執行計劃,進而引發查詢效能低落,假設我有一個 Member 資料表,Birthday 欄位有建立非叢集索引,資料表裡面有 100110 筆資料,其中 Birthday 的資料分佈為:

  1. "2001-01-01" 有 10 筆
  2. "2002-02-02" 有 100 筆
  3. "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 了,因此,如果參數嗅探並沒有為我們帶來太大的影響,那麼我們就應該盡量重覆利用快取的執行計劃,以免大量的執行計劃佔用太多的記憶體,即使要用這種手法來解決參數嗅探所引發的問題,那麼同一組參數所產生的註解應該要一樣。

最後,如果各位朋友也有遇過因為參數嗅探所引發的不同問題,還請不吝留言分享。

參考資料

C# 指南ASP.NET 教學ASP.NET MVC 指引
Azure SQL Database 教學SQL Server 教學Xamarin.Forms 教學