淺談Parameter Sniffing

情況

為何執行Stored Procedure有時會一下快有時一下慢?

為何同一句SQL包在Stored Procedure有時候很慢，但直接執行SQL Command就很快?相信大家都有類似的經驗，平常執行某支Stored Procedure時都跑的好好的，但是特定在某個時間點就跑的很慢，這種情況我也遇到過，我們來看看為何吧?

發現：

於是我觀察在特定時間點的參數與執行結果，發現在月底做月報表時呼叫Stored Procedure，時間參數區間會比較長，傳回結果也較多，但執行速度卻慢很多，但是同樣參數直接使用SQL Command差異卻不大，在摸不著頭緒時我就找G大神求救了。

思考：

很快的找到答案了，原來這種靈異現象是因為Parameter Sniffing而產生，(英文爛不知道怎麼翻譯就直接貼出來了)，那甚麼是Parameter Sniffing呢?

故事就要從頭說起了，SQL Server在第一次執行Stored Procedure時都會建立一個執行計畫優化執行效能，在建立執行計畫時會藉著第一次傳入的參數為Base而建立，Stored Procedure因傳入的參數而產生執行過程就是Parameter Sniffing。Parameter Sniffing所帶來的影響就是沒有效率的執行計畫進而讓直行效率下滑。

測試：

我們來實際測試一下，測試情境為使用不同的參數查詢資料筆數為1000000的資料表。

1.      建立測試資料表

2.      建立測試SP

3.      第一次測試SP

4.      第一次測試SP結果

測試結果得到資料筆數分別為109484與885，執行計畫兩者都是使用Index Scan(參數為1999-01-01到1999-12-31的執行計畫)，消耗的邏輯讀取IO皆為3593。

執行結果

執行計畫

執行SP所消耗的IO

5.      第二次測試SP

6.      第二次測試SP結果

測試結果得到資料筆數分別為885與109484，執行計畫兩者都是使用索引搜尋加上索引鍵查閱(參數為2005-01-01到2005-01-03的執行計畫)，消耗的邏輯讀取IO分別為2725與335550。

執行結果

執行計畫

執行SP所消耗的IO

解決方案：

如上述，Parameter Sniffing是第一次執行Stored Procedure 時，SQL Server根據傳入的參數，建立以該參數為基礎的最佳的執行計畫，要特別注意的是這裡說的第一次是指在compile 或recompile時還有沒任何procedure cache(我知道這一句有比較難懂)，我們可以藉由使用下列方式來避免Parameter Sniffing的發生。

(1)   使用RECOMPILE

使用RECOMPILE的好處就是每次執行時每次都用重新建立執行計畫，如此就可以避免Parameter Sniffing的發生。

(2)   使用LOCAL參數將Parameter Sniffing停用

在Stored Procedure宣告Loacl參數，使用Loacl參數存取外界傳進來的參數，如本範例使用@StartDate與@StopDate來存取@BeginDate與@EndDate，使用Loacl參數時當作WHERE條件時，由於SQL Server不知道 Local參數的內容為何，所以會建立一個通用的執行計畫。

(3)   依據需求建立多個適當的Stored Procedures

這個方法是依程式開發的需求，例如月報表就建立一支SP，而平日的日報表也建立一支SP，利用不同的SP來區隔不同情況的執行計畫。

結論：

有人會問使用Stored Procedures的其中一個優點不就是建立執行計畫的快取，下一次執行SP時就不用再次建立執行計畫進而提升效能，如果重新RECOMPILE不就是失去這項優勢了嗎?

我的想法是當重新產生執行計畫的Overhead遠小於Parameter Sniffing所帶來的衝擊，那就RECOMPILE吧，如果程式呼叫SP的條件與回傳資料數目差異不多時就用快取的執行計畫，簡而言之視情況而定。

此外同一句SQL的WHERE條件直接給予特定內容(如下圖)，執行SQL指令時就不會有Parameter Sniffing的情況，因為每一次執行時SQL Server Engine已經知道傳入的內容，故可以依據WHERE條件產生最佳的執行計畫，這就是為何同一句SQL包在Stored Procedure有時候很慢，但直接執行很快的原因了。

參考文章