SQL Server中參數化SQL寫法遇到parameter sniff ，導致不合理執行計畫重用的快速解決方案_MsSql

parameter sniff問題是重用其他參數產生的執行計畫，導致當前參數採用該執行計畫非最佳化的現象。想必熟悉資料的同學都應該知道，產生parameter sniff最典型的問題就是使用了參數化的SQL（或者預存程序中使用了參數化）寫法，如果存在資料分布不均勻的情況下，正常情況下產生的執行計畫，在傳入在分布資料較多的參數的情況下，重用了正常參數產生的執行計畫，而這種緩衝的執行計畫並非適合當前參數的一種情況。

這種情況，在實際業務中，出現的頻率還是比較高的，因為預存程序一般都是採用參數化的寫法，這時，遇到分布不均勻的資料參數時，parameter sniff現象就出現了，這種問題還是比較讓人頭疼的。

具體parameter sniff產生的原因，我就不做過多的解釋了，解釋這個就顯得太low了

我舉個簡單的例子，類比一下這個現象，說明參數化的存預存程序是怎麼寫的，存在哪些問題，又如何解決parameter sniff問題，

先建立一個測試環境：

create table ParameterSniffProblem(id int identity(1,1),CustomerId int,OrderId int,OrederStatus int,CreateDate Datetime,Remark varchar(200))declare @i int = 0while @i<500000beginINSERT INTO ParameterSniffProblem values (@i%10000,@i,RAND()*10,GETDATE()-RAND()*100,NEWID())set @i=@i+1end--假如某一個客戶有非常多的訂單，類比資料分布不均勻的情況INSERT INTO ParameterSniffProblem values (6666,RAND()*100000,1,GETDATE()-RAND()*100,NEWID())GO 100000--建立正常的索引CREATE CLUSTERED INDEX IDX_CreateDate on ParameterSniffProblem(CreateDate)
CREATE INDEX IDX_CustomerId ON ParameterSniffProblem(CustomerId)

參數化預存程序的寫法：

在編寫預存程序的時候，我們一般建議採用參數化的寫法，目的是為了減少預存程序的編譯和加強執行計畫緩衝的重用

大概是這樣子的

CREATE PROCEDURE [dbo].ParameterSniffTest ( @p_CustomerId int,@p_Status int,@p_FromDate datetime,@p_ToDate datetime) AS BEGINSET NOCOUNT ON DECLARE@Parm NVARCHAR(MAX),@sqlcommand NVARCHAR(MAX) = N''SET @sqlcommand = 'SELECT * FROM ParameterSniffProblem WHERE 1=1'　　　　 IF(@p_CustomerId IS NOT NULL)SET @sqlcommand = CONCAT(@sqlcommand,'AND CustomerId=@p_CustomerId ')IF(@p_Status IS NOT NULL)SET @sqlcommand = CONCAT(@sqlcommand,'AND OrederStatus=@p_Status ')IF(@p_FromDate IS NOT NULL)SET @sqlcommand = CONCAT(@sqlcommand,'AND CreateDate>=@p_FromDate ')IF(@p_ToDate IS NOT NULL)SET @sqlcommand = CONCAT(@sqlcommand,'AND CreateDate<=@p_ToDate ')　　　　SET @Parm= '@p_CustomerId int,@p_Status 　　int,@p_FromDate 　datetime,@p_ToDate 　　datetime '　　　　EXEC sp_executesql @sqlcommand,@Parm,@p_CustomerId = @p_CustomerId,@p_Status = @p_Status,@p_FromDate = @p_FromDate,@p_ToDate = @p_ToDate ENDGO

Parameter Sniff問題：

這就潛在一個parameter sniff問題，

比如我查詢使用者ID=100的訂單資訊，一個正常的分布的資料，預存程序第一次編譯，這個執行計畫完全沒有問題，

如果我接著改變參數執行查詢使用者6666的資訊，一個分布及其不均勻的資料，但是因為重用上面緩衝的執行計畫，就出現parameter sniff問題了，這個執行計畫顯然是不合理的

IO就不看了，刻意造的例子

如果我清空執行計畫緩衝，重新執行上述查詢，因為有了重編譯，執行計畫就是不這個樣子，對於CustomerID=6666這個參數來說，顯然走全表掃描代價要更小一點

想必這是一個開發中常見的問題給，我們參數化SQL就是為了讓不同參數的查詢重用執行計畫，但是很不幸，資料分布不均勻的時候，重用執行計畫恰恰又給資料庫造成了傷害，例中，如果是正常參數重用了分布較多資料的執行計畫，比如命名可以用到索引，結果是表掃描，後果會更嚴重。

那麼，既想要儘可能的重用執行計畫，又要避免因為執行計畫重用產生parameter sniff問題，怎麼辦？

我們知道問題在於@p_CustomerId身上，那麼可不可以對有可能產生parameter sniff問題的@p_CustomerId不做參數化，直接拼湊在SQL中，如果@p_CustomerId變化了就重編譯SQL，也就是對傳入進來的@p_CustomerId重編譯

如果是@p_CustomerId不變，其他參數有變化，比如這裡時間欄位的變化，還可以享受參數化帶來的執行計畫重用的好處 也就是這樣處理 @p_CustomerId這個參數，直接把@p_CustomerId以字串的方式平湊在SQL語句中，這樣的話，就相當於即席查詢了，不通過參數化的方式給CustomerId這個查詢條件欄位賦值

IF(@p_CustomerId IS NOT NULL)SET @sqlcommand = CONCAT(@sqlcommand,'AND CustomerId= ',@p_CustomerId)

這樣再去執行預存程序的時候，

帶入@p_CustomerId=1的時候，執行IDX_CustomerId的index seek

帶入@p_CustomerId=6666的時候，重編譯，執行計畫是全表掃描，避免重用上面產生的執行計畫，造成不合理的執行方式對效率以及資料庫伺服器資源的消耗

這樣會儘可能的減少parameter sniff問題帶來的影響，當緩衝了@p_CustomerId=1的執行計畫的時候，再次傳入@p_CustomerId=1，其他條件有較小的變化，比如時間欄位上有改動，依然可以重用緩衝的執行計畫，避免重編譯帶來的影響

結論：

這種方式於處理parameter sniff問題，當然不是完美的，肯定也有問題，我當然知道一旦@p_CustomerId不同就要重編譯

肯定會因為@p_CustomerId參數值不同，這樣的話，不可避免地增加了重編譯的機會，

但是卻不會因為不合理的執行計畫重用，帶來的parameter sniff問題

要知道一旦產生parameter sniff問題，大量的查詢用到不合理的執行計畫，會對整個伺服器產生非常嚴重的影響，比如可能會產生大量的IO等

同時存在一個好處，比如第一次傳入@p_CustomerId=1，

再次傳入@p_CustomerId=1，其他條件有較小的變化，比如時間欄位上有改動，依然可以重用緩衝的執行計畫，避免重編譯帶來的影響當然我這裡只是一個簡單的例子，實際應用中遠遠比這個複雜

比如分布的特別的多的資料有兩個特點，第一分布的標示不僅僅只有一個，第二分布不均的資料是動態，有可能第一季度是A這部分資料佔據大多數，有可能是第二季度B資料占絕大多數

所以很難採用Plan Guide的方式解決parameter sniff問題

這種方式可以在一定程度上也能夠重用緩衝的執行計畫，可以減少（但不可避免）重編譯的次數

同時，這種方式與拼湊一個SQL字串執行的即席查詢方式相比，同時還可以利用參數化帶來的其他好處，比如SQL注入等等

總結：

　　　　parameter sniff問題的解決方式有很多，不一一囉嗦了

　　　　最典型的就是強制重編譯，

　　　　或者使用EXEC執行一個拼湊出來的字串，這種方式屬於Adhoc查詢

　　　　或者查詢提示，

　　　　或者是使用本地變數，

　　　   或者使用Plan Guide等等等等，

　　　　每種方式都有他的局限性，至少到目前為止，還沒有一種十全十美的方式來解決parameter sniff問題

　　　　遇到問題，解決方案有很多種，以最小的代價解決問題才是王道。