2017年7月28日 星期五

[SQL Server] 如何解決不佳的Parameter sniffing所造成的效能問題

前言
從上一篇文章中 : https://retrydb.blogspot.tw/2017/07/sql-server-parameter-sniffing.html
我們了解
Parameter sniffing的目的是為了讓Plan可以重複使用,避免每次執行Stored procedure都必須耗費CPU編譯其語法來選擇其演算法。
同時,我們也了解到,如果第一次執行所選擇的是資料分布非常極端的情況,可能造成之後在執行此Stored procedure時,所衍生出的效能問題。
本篇會介紹幾個情境如何避免Bad Parameter sniffing

解決方案探討

解決方案1.
  Recompile : 極少執行,但每次所進行查詢的資料量差異極大

如果我們的語法並不常常執行,但每次執行時都查詢資料量的結果集差異非常大,這邊建議的解決方案是每次執行的時候去Recompile(重新編譯)你的Stored Procedure
相信許多人聽到Recompile避之唯恐不及,Recompile是一把雙面刃,如果你的語法查詢的頻率非常頻繁,它就會耗費大量的CPUCompile,造成 CPU High的嚴重問題。但如果使用得宜,它能讓你的查詢選擇最佳的執行計畫,避免Parameter sniffing所造成的效能問題。
相比不當
Parameter sniffing所耗費的成本,Recompile所耗費的成本是非常值得投資的。

Recompile有幾個做法:

針對整個Procedure層級:

只要在Stored Procedure的開頭之前加上WITH RECOMPILE,在每次執行此Stored Procedure時都會重新編譯此Stored Procedure,而且不存放PlanCache中。
接下來的所有語法我們都拿上一篇的Stored Procedure進行修改及測試。
建立WITH RECOMPILEStored Procedure

if
object_id('dbo.GetOrderByCustomerID', 'p') is null
    exec ('create procedure dbo.GetOrderByCustomerID as select 1')
go
ALTER PROC dbo.GetOrderByCustomerID
    @CustomerID 
BIGIN TWITH RECOMPILE
AS
SELECT OrderID, CustomerID, ProductID, DateCreated
FROM dbo.Orders
WHERE CustomerID = @CustomerID


接下來我們分別執行不同參數查看其執行計畫

EXEC
dbo.GetOrderByCustomerID 1 -- 選擇性低的












EXEC dbo.GetOrderByCustomerID 2 -- 選擇性高的











我們查看Cache中是否其執行計劃
SELECT Cacheobjtype, Objtype, TEXT, query_plan
FROM sys.dm_exec_cached_plans t1
CROSS APPLY sys.dm_exec_sql_text(plan_handle) t2
CROSS APPLY sys.dm_exec_query_plan(plan_handle) t3
WHERE t2.objectid = object_id('dbo.GetOrderByCustomerID', 'p')








只要加了Recompile,並不會在Cache中存放執行計劃。

針對Stored Procedure中個別語法進行Recompile

個別語法的結尾時,加上OPTION(RECOMPILE) 即可達成此成效。
我們這邊特別在Procedure中,加上兩段同樣的語法,但差異只在有無Recompile,並查看Cache中的Plan

if object_id('dbo.GetOrderByCustomerID', 'p') is null
    exec ('create procedure dbo.GetOrderByCustomerID as select 1')
go
ALTER PROC dbo.GetOrderByCustomerID
    @CustomerID BIGINT
AS
BEGIN
    SELECT OrderID, CustomerID, ProductID, DateCreated
    FROM dbo.Orders
    WHERE CustomerID = @CustomerID OPTION(RECOMPILE) 

    SELECT OrderID, CustomerID, ProductID, DateCreated
    FROM dbo.Orders
    WHERE CustomerID = @CustomerID
END 


接下來我們分別執行不同參數查看其執行計畫
EXEC
dbo.GetOrderByCustomerID 1 -- 選擇性低的




















EXEC dbo.GetOrderByCustomerID 2 -- 選擇性高的





















我們可以發現上半段有添加OPTION(RECOMPILE)的語法會重新編譯選擇Index Seek + Key Lookup

但下半段則會沿用第一次執行時所採用的Index Scan
很明顯可以看出下面未進行Recompile的Cost是較高的。

接下來我們查看Cache是否有我們的執行計劃

裡面是有存放執行計劃的,但每次執行會去重新修改Recompile那一段的執行計劃在寫回Cache。







解決方案2.  OPTIMIZE FOR UNKNOWN / Local Variable  : 頻繁查詢,中庸之道的選擇

假設你的語法頻繁查詢,RECOMPILE顯然不是一個非常好的方法。
如果你不追求每次的查詢都是完美的,我們可以透過OPTIMIZE FOR UNKNOWN 或者Local Variable來解決Bad Parameter sniffing

先來了解OPTIMIZE FOR UNKNOWN的原理,他是讓Query Optimizer在編譯時,告訴其參數是未知的,只要Query Optimizer認定參數的值是未知的,他會選擇一個中庸的值,如果是等於( = )的運算子,他的預估行數會是 :  Total Rows / (Distinct Column組合數)
我們來看實際的例子:
if object_id('dbo.GetOrderByCustomerID', 'p') is null
    exec ('create procedure dbo.GetOrderByCustomerID as select 1')
go
ALTER PROC dbo.GetOrderByCustomerID
    @CustomerID BIGINT
AS
BEGIN
    SELECT OrderID, CustomerID, ProductID, DateCreated
    FROM dbo.Orders
    WHERE CustomerID = @CustomerID OPTION(OPTIMIZE FOR UNKNOWN)
END 

接下來我們分別執行不同參數查看其執行計畫

EXEC dbo.GetOrderByCustomerID 1 -- 選擇性低的














我們特別查看其估計行數



















5005是怎麼得出來的呢?

我們回顧上一篇的查詢資料分佈

SELECT CustomerID, COUNT(*) AS OrderCount
FROM dbo.Orders
GROUP BY CustomerID









其計算方法為: Total Rows : 10010 / DistinctColumn的組合數 : 2 = 5005
Optimizer只要判定參數為未知就會用這方法取個中庸的值來產生出執行計劃
如果是 非等於的Operator,例如 : >,預估行數則會是全部的Row30%

我們做個簡單測試 :

if object_id('dbo.GetOrderByCustomerID', 'p') is null
    exec ('create procedure dbo.GetOrderByCustomerID as select 1')
go
ALTER PROC dbo.GetOrderByCustomerID
    @CustomerID BIGINT
AS
BEGIN
    SELECT OrderID, CustomerID, ProductID, DateCreated
    FROM dbo.Orders
    WHERE CustomerID > @CustomerID OPTION(OPTIMIZE FOR UNKNOWN)
END


此次執行此Stored Procedure查看其執行計劃及預估行數





























預估行數的3003 即是 Total Rows : 10010 * 0.3 = 3003 這個方法所推估出來的

這個方法所選擇的執行計劃不會針對每次執行選擇最完美的做法,但其所選擇的中庸之值可以避免大部分的查詢有效能問題。


不過OPTIMIZE FOR UNKNOWN只適用2008之後的版本。

如果是2008已前的版本,也有其他解決方案,就是Local Variable,透過Local variable承接傳進來的參數,亦可達到與Local variable一樣的效果,只可惜寫起來可讀性差了點。

if object_id('dbo.GetOrderByCustomerID', 'p') is null
    exec ('create procedure dbo.GetOrderByCustomerID as select 1')
go
ALTER PROC dbo.GetOrderByCustomerID
    @CustomerID BIGINT
AS
BEGIN
    DECLARE @LocalCustomerID BIGINT = @CustomerID --Assign parameter to local variable
    SELECT OrderID, CustomerID, ProductID, DateCreated
    FROM dbo.Orders
    WHERE CustomerID = @LocalCustomerID
END
GO

EXEC dbo.GetOrderByCustomerID 1 -- 選擇性低的















預估行數:

















解決方案3.  分成多隻Stored Procedure: 適合頻繁查詢,且追求每次查詢都能有近乎完美效能的解法


如果你對於你的系統夠了解,參數的可能性不多且你能掌握每個參數的選擇性時,你可以透過建立多隻Stored Procedure並根據參數自行配置Hint指定選擇演算法。

這個方法算是Hardcode的方法,如果對於自己系統不夠掌握千萬別這麼做,可讀性也相對降低許多。

Customer來做例子不太好,因為我們很難掌控顧客的數量及其購買量,一旦有新的顧客又要調整Stored Procedure,並不適合這解決方案。

我們用以Update為主的案例來實際測試

我們建立一個Table,在商業上它固定一萬筆,且不會在新增,但會頻繁的去Update
更新資料都是批次處理,而且相當的頻繁

IF (EXISTS (SELECT *
    FROM INFORMATION_SCHEMA.TABLES
    WHERE TABLE_SCHEMA = 'dbo'
    AND  TABLE_NAME = 'SelectionSetting'))
BEGIN
    DROP TABLE dbo.SelectionSetting
END
GO
CREATE TABLE dbo.SelectionSetting
(
    SelectionID INT NOT NULL PRIMARY KEY,
    Rate DECIMAL(10,2) NOT NULL
)
GO

DECLARE @Count INT = 1

WHILE(@Count <= 10000)
BEGIN
    INSERT INTO dbo.SelectionSetting
    (
        SelectionID,
        Rate
    )
    SELECT @Count
         , RAND() * 100

    SET @Count = @Count + 1
END
GO

接下來我們需要透過User Defined Table Type當做參數傳進去Stored Procedure來進行批次的更

IF NOT EXISTS (SELECT * FROM sys.types
               WHERE is_table_type = 1 AND name = 'uftt_SelectionSetting')
BEGIN
    CREATE TYPE dbo.uftt_SelectionSetting AS TABLE
    (
        SelectionID INT NOT NULL PRIMARY KEY,
        Rate DECIMAL(10,2) NOT NULL
    )
END
GO
我們根據需求建立Stored Procedure
CREATE PROCEDURE dbo.UpdateSelectionSetting
    @RateSetting dbo.uftt_SelectionSetting READONLY
AS
UPDATE ss
SET ss.Rate = rs.Rate
FROM dbo.SelectionSetting ss
INNER JOIN @RateSetting rs
    ON ss.SelectionID = rs.SelectionID

分別測試大資料量的Batch跟小資料量的Batch,並清除BufferCache觀察其最佳執行計劃

先進行小資料量的測試
GO
CHECKPOINT;
GO
DBCC FREEPROCCACHE
DBCC DROPCLEANBUFFERS
GO
DECLARE @RateSetting dbo.uftt_SelectionSetting;
INSERT INTO @RateSetting
SELECT TOP 10 SelectionID, RAND() * 100
FROM dbo.SelectionSetting

EXEC dbo.UpdateSelectionSetting @RateSetting

根據執行計劃,會透過Seek先找出小量的結果集在進行更新










接下來清除CacheBuffer在執行大資料量的Batch Update
GO
CHECKPOINT;
GO
DBCC FREEPROCCACHE
DBCC DROPCLEANBUFFERS
GO

DECLARE @RateSetting dbo.uftt_SelectionSetting;

INSERT INTO @RateSetting
SELECT TOP 3500 SelectionID, RAND() * 100
FROM dbo.SelectionSetting

EXEC dbo.UpdateSelectionSetting @RateSetting










透過執行計劃可以知道與其去Seek,不如直接Scan整個Table找出結果集來進行更新來的快

配合這種情境 ,我們可以建立兩隻Stored Procedure,分別處理大Batch及小Batch,並加上Hint,避免特殊情況造成不當Sniffing


CREATE PROCEDURE dbo.UpdateSelectionSetting_BigBatch
    @RateSetting dbo.uftt_SelectionSetting READONLY
AS
UPDATE ss
SET ss.Rate = rs.Rate
FROM dbo.SelectionSetting ss WITH (FORCESCAN)
INNER JOIN @RateSetting rs
    ON ss.SelectionID = rs.SelectionID

GO
CREATE PROCEDURE dbo.UpdateSelectionSetting_SmallBatch
   @RateSetting dbo.uftt_SelectionSetting READONLY
AS
UPDATE ss
SET ss.Rate = rs.Rate
FROM dbo.SelectionSetting ss WITH(FORCESEEK)
INNER JOIN @RateSetting rs
    ON ss.SelectionID = rs.SelectionID

GO

並建立一支Stored Procedure當做入口,根據傳進來的資料量選擇適當的Stored Procedure

CREATE PROCEDURE dbo.UpdateSelectionSetting_Entry
    @RateSetting dbo.uftt_SelectionSetting READONLY
AS
BEGIN
    DECLARE @Count INT;
    SELECT @Count = COUNT(*) FROM @RateSetting
    IF(@Count > 3000)
        EXEC dbo.UpdateSelectionSetting_BigBatch @RateSetting
    ELSE
        EXEC dbo.UpdateSelectionSetting_SmallBatch @RateSetting
END

我們實際來測試一下兩者的差異

我們透過SQL Query Stress  模擬8Thread,分別從1,  501, 1001....9501筆資料量進行更新

這是原本單一Stored Procedure的效能




















拆成兩隻Stored Procedure並透過Entry Stored Procedure進行更新



















完成時間差了近3秒鐘,而且平均每次執行的Logic Reads 差了180000,可以看出很明顯的差異

後記
要解決Parameter sniffing在極端資料分佈時所造成的問題,並沒有符合所有情境的單一最佳解,其實還是要根據個案做分析。
最後我們簡單總結一下今天提到的三個解決方案:

1.  Recompile Stored Procedure -  適用於不常執行的查詢,且每次查詢的資料量差異極大

2.  Optimize for unknown /  Local Variable   -  適用於頻繁查詢,不追求每次查詢的極致效能,選擇的中庸之道,可以解決大部分的Sniffing的問題

3.  Multi Stored Procedure  -  頻繁查詢,在對商業邏輯有非常充分了解時,可以選擇此方法來進行Tuning,兼顧大資料及小資料量的查詢效能。
下一篇會介紹一個誤用Local Variable所造成的嚴重效能問題,及最後選擇的解決方案。

沒有留言:

張貼留言