前言
從上一篇文章中 : https://retrydb.blogspot.tw/2017/07/sql-server-parameter-sniffing.html
我們了解Parameter sniffing的目的是為了讓Plan可以重複使用，避免每次執行Stored procedure都必須耗費CPU編譯其語法來選擇其演算法。
我們了解Parameter sniffing的目的是為了讓Plan可以重複使用，避免每次執行Stored procedure都必須耗費CPU編譯其語法來選擇其演算法。
同時，我們也了解到，如果第一次執行所選擇的是資料分布非常極端的情況，可能造成之後在執行此Stored procedure時，所衍生出的效能問題。
本篇會介紹幾個情境如何避免Bad
Parameter sniffing。
解決方案探討
解決方案1. Recompile : 極少執行，但每次所進行查詢的資料量差異極大
解決方案1. Recompile : 極少執行，但每次所進行查詢的資料量差異極大
如果我們的語法並不常常執行，但每次執行時都查詢資料量的結果集差異非常大，這邊建議的解決方案是每次執行的時候去Recompile(重新編譯)你的Stored Procedure。
相信許多人聽到Recompile避之唯恐不及，Recompile是一把雙面刃，如果你的語法查詢的頻率非常頻繁，它就會耗費大量的CPU來Compile，造成 CPU High的嚴重問題。但如果使用得宜，它能讓你的查詢選擇最佳的執行計畫，避免Parameter sniffing所造成的效能問題。
相比不當Parameter sniffing所耗費的成本，Recompile所耗費的成本是非常值得投資的。
Recompile有幾個做法:
相比不當Parameter sniffing所耗費的成本，Recompile所耗費的成本是非常值得投資的。
Recompile有幾個做法:
．針對整個Procedure層級:
只要在Stored Procedure的開頭之前加上WITH RECOMPILE，在每次執行此Stored Procedure時都會重新編譯此Stored Procedure，而且不存放Plan到Cache中。
接下來的所有語法我們都拿上一篇的Stored Procedure進行修改及測試。只要在Stored Procedure的開頭之前加上WITH RECOMPILE，在每次執行此Stored Procedure時都會重新編譯此Stored Procedure，而且不存放Plan到Cache中。
建立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 -- 選擇性低的
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。
裡面是有存放執行計劃的，但每次執行會去重新修改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
我們來看實際的例子：
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')
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承接傳進來的參數，亦可達到與OPTIMIZE FOR UNKNOWN一樣的效果，只可惜寫起來可讀性差了點。
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，並清除Buffer及Cache觀察其最佳執行計劃
先進行小資料量的測試
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先找出小量的結果集在進行更新
接下來清除Cache及Buffer在執行大資料量的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 模擬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所造成的嚴重效能問題，及最後選擇的解決方案。
要解決Parameter sniffing在極端資料分佈時所造成的問題，並沒有符合所有情境的單一最佳解，其實還是要根據個案做分析。
最後我們簡單總結一下今天提到的三個解決方案：
1. Recompile Stored Procedure - 適用於不常執行的查詢，且每次查詢的資料量差異極大
2. Optimize for unknown / Local Variable - 適用於頻繁查詢，不追求每次查詢的極致效能，選擇的中庸之道，可以解決大部分的Sniffing的問題
3. Multi Stored Procedure - 頻繁查詢，在對商業邏輯有非常充分了解時，可以選擇此方法來進行Tuning，兼顧大資料及小資料量的查詢效能。
下一篇會介紹一個誤用Local Variable所造成的嚴重效能問題，及最後選擇的解決方案。
非常實用的文章資訊，感謝教學分享 ^__^
回覆刪除