Retry : [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是一把雙面刃，如果你的語法查詢的頻率非常頻繁，它就會耗費大量的CPU來Compile，造成 CPU High的嚴重問題。但如果使用得宜，它能讓你的查詢選擇最佳的執行計畫，避免Parameter sniffing所造成的效能問題。
相比不當Parameter sniffing所耗費的成本，Recompile所耗費的成本是非常值得投資的。

Recompile有幾個做法:

．針對整個Procedure層級:

只要在Stored Procedure的開頭之前加上WITH RECOMPILE，在每次執行此Stored Procedure時都會重新編譯此Stored Procedure，而且不存放Plan到Cache中。

接下來的所有語法我們都拿上一篇的Stored Procedure進行修改及測試。
建立WITH RECOMPILE的Stored Procedure
if object_id('dbo.GetOrderByCustomerID', 'p') is null
exec ('create procedure dbo.GetOrderByCustomerID as select 1')
go
ALTER PROC dbo.GetOrderByCustomerID
@CustomerID BIGINT
WITH 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 / Distinct此Column的組合數 : 2 = 5005

Optimizer只要判定參數為未知就會用這方法取個中庸的值來產生出執行計劃

如果是非等於的Operator，例如 : >，預估行數則會是全部的Row的30%

我們做個簡單測試 :

if object_id('dbo.GetOrderByCustomerID', 'p') is null

exec ('create procedure dbo.GetOrderByCustomerID as select 1')

ALTER PROC dbo.GetOrderByCustomerID

@CustomerID BIGINT

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承接傳進來的參數，亦可達到與OPTIMIZE FOR UNKNOWN一樣的效果，只可惜寫起來可讀性差了點。

if object_id('dbo.GetOrderByCustomerID', 'p') is null

exec ('create procedure dbo.GetOrderByCustomerID as select 1')

ALTER PROC dbo.GetOrderByCustomerID

@CustomerID BIGINT

BEGIN

DECLARE @LocalCustomerID BIGINT = @CustomerID --Assign parameter to local variable

SELECT OrderID, CustomerID, ProductID, DateCreated

FROM dbo.Orders

WHERE CustomerID = @LocalCustomerID

END

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

CREATE TABLE dbo.SelectionSetting

(

SelectionID INT NOT NULL PRIMARY KEY,

Rate DECIMAL(10,2) NOT NULL

)

DECLARE @Count INT = 1

WHILE(@Count <= 10000)

BEGIN

INSERT INTO dbo.SelectionSetting

(

SelectionID,

Rate

)

SELECT @Count

, RAND() * 100

SET @Count = @Count + 1

END

接下來我們需要透過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

我們根據需求建立Stored Procedure

CREATE PROCEDURE dbo.UpdateSelectionSetting

@RateSetting dbo.uftt_SelectionSetting READONLY

UPDATE ss

SET ss.Rate = rs.Rate

FROM dbo.SelectionSetting ss

INNER JOIN @RateSetting rs

ON ss.SelectionID = rs.SelectionID

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

先進行小資料量的測試

CHECKPOINT;

DBCC FREEPROCCACHE

DBCC DROPCLEANBUFFERS

DECLARE @RateSetting dbo.uftt_SelectionSetting;

INSERT INTO @RateSetting

SELECT TOP 10 SelectionID, RAND() * 100

FROM dbo.SelectionSetting

EXEC dbo.UpdateSelectionSetting @RateSetting

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

接下來清除Cache及Buffer在執行大資料量的Batch Update

CHECKPOINT;

DBCC FREEPROCCACHE

DBCC DROPCLEANBUFFERS

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

UPDATE ss

SET ss.Rate = rs.Rate

FROM dbo.SelectionSetting ss WITH (FORCESCAN)

INNER JOIN @RateSetting rs

ON ss.SelectionID = rs.SelectionID

CREATE PROCEDURE dbo.UpdateSelectionSetting_SmallBatch

@RateSetting dbo.uftt_SelectionSetting READONLY

UPDATE ss

SET ss.Rate = rs.Rate

FROM dbo.SelectionSetting ss WITH(FORCESEEK)

INNER JOIN @RateSetting rs

ON ss.SelectionID = rs.SelectionID

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

CREATE PROCEDURE dbo.UpdateSelectionSetting_Entry

@RateSetting dbo.uftt_SelectionSetting READONLY

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 模擬8條Thread，分別從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所造成的嚴重效能問題，及最後選擇的解決方案。

Retry

2017年7月28日星期五

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

1 則留言:

2017年7月28日 星期五

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

1 則留言:

2017年7月28日星期五