SQL Server中參數(shù)化SQL寫法遇到parameter sniff ，導(dǎo)致不合理執(zhí)行計(jì)劃重用的快速解決方法

parameter sniff問題是重用其他參數(shù)生成的執(zhí)行計(jì)劃，導(dǎo)致當(dāng)前參數(shù)采用該執(zhí)行計(jì)劃非最優(yōu)化的現(xiàn)象。想必熟悉數(shù)據(jù)的同學(xué)都應(yīng)該知道，產(chǎn)生parameter sniff最典型的問題就是使用了參數(shù)化的SQL（或者存儲(chǔ)過程中使用了參數(shù)化）寫法，如果存在數(shù)據(jù)分布不均勻的情況下，正常情況下生成的執(zhí)行計(jì)劃，在傳入在分布數(shù)據(jù)較多的參數(shù)的情況下，重用了正常參數(shù)生成的執(zhí)行計(jì)劃，而這種緩存的執(zhí)行計(jì)劃并非適合當(dāng)前參數(shù)的一種情況。

這種情況，在實(shí)際業(yè)務(wù)中，出現(xiàn)的頻率還是比較高的，因?yàn)榇鎯?chǔ)過程一般都是采用參數(shù)化的寫法，這時(shí)，遇到分布不均勻的數(shù)據(jù)參數(shù)時(shí)，parameter sniff現(xiàn)象就出現(xiàn)了，這種問題還是比較讓人頭疼的。

具體parameter sniff產(chǎn)生的原因，我就不做過多的解釋了，解釋這個(gè)就顯得太low了

我舉個(gè)簡單的例子，模擬一下這個(gè)現(xiàn)象，說明參數(shù)化的存存儲(chǔ)過程是怎么寫的，存在哪些問題，又如何解決parameter sniff問題，

先創(chuàng)建一個(gè)測(cè)試環(huán)境：

create table ParameterSniffProblem
(
id int identity(1,1),
CustomerId int,
OrderId int,
OrederStatus int,
CreateDate Datetime,
Remark varchar(200)
)
declare @i int = 0
while @i500000
begin
INSERT INTO ParameterSniffProblem values (@i%10000,@i,RAND()*10,GETDATE()-RAND()*100,NEWID())
set @i=@i+1
end
--假如某一個(gè)客戶有非常多的訂單，模擬數(shù)據(jù)分布不均勻的情況
INSERT INTO ParameterSniffProblem values (6666,RAND()*100000,1,GETDATE()-RAND()*100,NEWID())
GO 100000
--創(chuàng)建正常的索引
CREATE CLUSTERED INDEX IDX_CreateDate on ParameterSniffProblem(CreateDate
)
CREATE INDEX IDX_CustomerId ON ParameterSniffProblem(CustomerId)

參數(shù)化存儲(chǔ)過程的寫法：

在編寫存儲(chǔ)過程的時(shí)候，我們一般建議采用參數(shù)化的寫法，目的是為了減少存儲(chǔ)過程的編譯和加強(qiáng)執(zhí)行計(jì)劃緩存的重用

大概是這樣子的

CREATE PROCEDURE [dbo].ParameterSniffTest 
( 
@p_CustomerId int,
@p_Status int,
@p_FromDate datetime,
@p_ToDate datetime
) 
AS 
BEGIN
SET 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 
END
GO

Parameter Sniff問題：

這就潛在一個(gè)parameter sniff問題，

比如我查詢用戶ID=100的訂單信息，一個(gè)正常的分布的數(shù)據(jù)，存儲(chǔ)過程第一次編譯，這個(gè)執(zhí)行計(jì)劃完全沒有問題，

如果我接著改變參數(shù)執(zhí)行查詢用戶6666的信息，一個(gè)分布及其不均勻的數(shù)據(jù)，但是因?yàn)橹赜蒙厦婢彺娴膱?zhí)行計(jì)劃，就出現(xiàn)parameter sniff問題了，這個(gè)執(zhí)行計(jì)劃顯然是不合理的

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

如果我清空?qǐng)?zhí)行計(jì)劃緩存，重新執(zhí)行上述查詢，因?yàn)橛辛酥鼐幾g，執(zhí)行計(jì)劃就是不這個(gè)樣子，對(duì)于CustomerID=6666這個(gè)參數(shù)來說，顯然走全表掃描代價(jià)要更小一點(diǎn)

想必這是一個(gè)開發(fā)中常見的問題給，我們參數(shù)化SQL就是為了讓不同參數(shù)的查詢重用執(zhí)行計(jì)劃，但是很不幸，數(shù)據(jù)分布不均勻的時(shí)候，重用執(zhí)行計(jì)劃恰恰又給數(shù)據(jù)庫造成了傷害，例中，如果是正常參數(shù)重用了分布較多數(shù)據(jù)的執(zhí)行計(jì)劃，比如命名可以用到索引，結(jié)果是表掃描，后果會(huì)更嚴(yán)重。

那么，既想要盡可能的重用執(zhí)行計(jì)劃，又要避免因?yàn)閳?zhí)行計(jì)劃重用產(chǎn)生parameter sniff問題，怎么辦？

我們知道問題在于@p_CustomerId身上，那么可不可以對(duì)有可能產(chǎn)生parameter sniff問題的@p_CustomerId不做參數(shù)化，直接拼湊在SQL中，如果@p_CustomerId變化了就重編譯SQL，也就是對(duì)傳入進(jìn)來的@p_CustomerId重編譯

如果是@p_CustomerId不變，其他參數(shù)有變化，比如這里時(shí)間字段的變化，還可以享受參數(shù)化帶來的執(zhí)行計(jì)劃重用的好處 也就是這樣處理 @p_CustomerId這個(gè)參數(shù)，直接把@p_CustomerId以字符串的方式平湊在SQL語句中，這樣的話，就相當(dāng)于即席查詢了，不通過參數(shù)化的方式給CustomerId這個(gè)查詢條件字段賦值

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

這樣再去執(zhí)行存儲(chǔ)過程的時(shí)候，

帶入@p_CustomerId=1的時(shí)候，執(zhí)行IDX_CustomerId的index seek

帶入@p_CustomerId=6666的時(shí)候，重編譯，執(zhí)行計(jì)劃是全表掃描，避免重用上面生成的執(zhí)行計(jì)劃，造成不合理的執(zhí)行方式對(duì)效率以及數(shù)據(jù)庫服務(wù)器資源的消耗

這樣會(huì)盡可能的減少parameter sniff問題帶來的影響，當(dāng)緩存了@p_CustomerId=1的執(zhí)行計(jì)劃的時(shí)候，再次傳入@p_CustomerId=1，其他條件有較小的變化，比如時(shí)間字段上有改動(dòng)，依然可以重用緩存的執(zhí)行計(jì)劃，避免重編譯帶來的影響

結(jié)論：

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

肯定會(huì)因?yàn)锧p_CustomerId參數(shù)值不同，這樣的話，不可避免地增加了重編譯的機(jī)會(huì)，

但是卻不會(huì)因?yàn)椴缓侠淼膱?zhí)行計(jì)劃重用，帶來的parameter sniff問題

要知道一旦產(chǎn)生parameter sniff問題，大量的查詢用到不合理的執(zhí)行計(jì)劃，會(huì)對(duì)整個(gè)服務(wù)器產(chǎn)生非常嚴(yán)重的影響，比如可能會(huì)產(chǎn)生大量的IO等

同時(shí)存在一個(gè)好處，比如第一次傳入@p_CustomerId=1，

再次傳入@p_CustomerId=1，其他條件有較小的變化，比如時(shí)間字段上有改動(dòng)，依然可以重用緩存的執(zhí)行計(jì)劃，避免重編譯帶來的影響當(dāng)然我這里只是一個(gè)簡單的例子，實(shí)際應(yīng)用中遠(yuǎn)遠(yuǎn)比這個(gè)復(fù)雜

比如分布的特別的多的數(shù)據(jù)有兩個(gè)特點(diǎn)，第一分布的標(biāo)示不僅僅只有一個(gè)，第二分布不均的數(shù)據(jù)是動(dòng)態(tài)的，有可能第一季度是A這部分?jǐn)?shù)據(jù)占據(jù)大多數(shù)，有可能是第二季度B數(shù)據(jù)占絕大多數(shù)

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

這種方式可以在一定程度上也能夠重用緩存的執(zhí)行計(jì)劃，可以減少（但不可避免）重編譯的次數(shù)

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

總結(jié)：

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

　　　　最典型的就是強(qiáng)制重編譯，

　　　　或者使用EXEC執(zhí)行一個(gè)拼湊出來的字符串，這種方式屬于Adhoc查詢

　　　　或者查詢提示，

　　　　或者是使用本地變量，

　　　   或者使用Plan Guide等等等等，

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

　　　　遇到問題，解決方法有很多種，以最小的代價(jià)解決問題才是王道。