程序師世界是廣大編程愛好者互助、分享、學習的平台,程序師世界有你更精彩!
首頁
編程語言
C語言|JAVA編程
Python編程
網頁編程
ASP編程|PHP編程
JSP編程
數據庫知識
MYSQL數據庫|SqlServer數據庫
Oracle數據庫|DB2數據庫
 程式師世界 >> 數據庫知識 >> SqlServer數據庫 >> 關於SqlServer >> SQL Server中參數化SQL寫法遇到parameter sniff ,導致不合理執行計劃重用的快速解決方法

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

編輯:關於SqlServer

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 = 0
while @i<500000
begin
INSERT INTO ParameterSniffProblem values (@i%10000,@i,RAND()*10,GETDATE()-RAND()*100,NEWID())
set @i=@i+1
end
--假如某一個客戶有非常多的訂單,模擬數據分布不均勻的情況
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 
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問題:

這就潛在一個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問題

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

  1. 上一頁:
  2. 下一頁:
Copyright © 程式師世界 All Rights Reserved