2017年7月23日 星期日

[SQL Server] 淺談Parameter sniffing (一)

什麼是Parameter sniffing?

SQL Server為了避免在Cache有許多重覆的執行計畫,當你的語法是參數化的,且沒有任何的PlanCache中時,會根據你當時的參數產生一份最恰當的執行計畫,爾後除非recompile stored procedure,否則就會一直重用這份執行計畫來選擇是否要Scan/Seek table、選擇哪種Join方式(註解1)、所有相關的運算方法…等等。


※註解1: 這邊指的Join並非Inner JoinLeft JoinCross Join,而是在背後進行兩個表的聯集所選擇的不同的演算法,目前有三種Join方式:Loop JoinMerge JoinHash Join。之後有機會會詳細介紹。

我們來簡單做個模擬:
先建立一個OrdersTable,並在CustomerID建立Non clustered index
IF (EXISTS (SELECT *
    FROM INFORMATION_SCHEMA.TABLES
    WHERE TABLE_SCHEMA = 'dbo'
    AND  TABLE_NAME = 'Orders'))
BEGIN
    DROP TABLE dbo.Orders
END
GO
CREATE TABLE dbo.Orders
(
    OrderID BIGINT IDENTITY(1,1) PRIMARY KEY CLUSTERED,
    CustomerID BIGINT NOT NULL,
    ProductID INT NOT NULL,
    DateCreated DATETIME2(3) NOT NULL
)
CREATE NONCLUSTERED INDEX IX_Orders_CustomerID ON dbo.Orders
(
    CustomerID
)
GO

我們刻意模擬極端的資料分布,打10000CustomerID = 1的資料以及10CustomerID = 2的資料
--Prepare Initial Data
DECLARE @coun INT = 0;
WHILE(@coun < 10000)
BEGIN
    INSERT INTO dbo.Orders
    (
        CustomerID,
        ProductID,
        DateCreated
    )
    VALUES(1, 1, SYSDATETIME())

    SET @coun = @coun + 1;
END

SET @coun = 0;
WHILE(@coun < 10)
BEGIN
    INSERT INTO dbo.Orders
    (
        CustomerID,
        ProductID,
        DateCreated
    )
    VALUES(2, 1, SYSDATETIME())

    SET @coun = @coun + 1;
END
GO

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







接下來我們建立Stored Procedure,來透過CustomerID查詢Order
CREATE PROC dbo.GetOrderByCustomerID
    @CustomerID BIGINT
AS
SELECT OrderID, CustomerID, ProductID, DateCreated
FROM dbo.Orders
WHERE CustomerID = @CustomerID

在執行之前我們先查詢Cache中是否有相關的execution plan
 
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')








執行第一次的查詢,並開啟Included actual execution plan,查詢條件為CustomerID = 1
EXEC dbo.GetOrderByCustomerID 1


由於CustomerID 占了非常大的百分比,Optimizer根據我們的參數幫我們選擇了Index Scan而非Index Seek + Key Lookup,透過上面的語法在查詢一次,可以發現在Cache中儲存了此份執行計畫。
根據第一次查詢的參數,產生最恰當的執行計畫,這行為便稱之為
Parameter sniffing



接下來我們進行第二次的查詢,查詢條件為CustomerID = 2
EXEC dbo.GetOrderByCustomerID 2


可以發現明明這段語法選擇性非常高(能從大量的資料中查詢出少量的資料),卻選擇了Index Scan而非Index Seek + Key Lookup
原因是因為SQL Server發現Cache中已經有這隻Stored procedurePlan,就重複使用了這個執行計畫所規劃的演算法來執行所有查詢。

原本Parameter sniffing是一番美意,幫助我們重覆使用執行計畫,避免Memory大量產生重覆的執行計畫,而且每次執行Stored Procedure都要花費額外的效能來根據參數編譯。
但在這種資料分佈很極端的情況,如果第一次查詢選擇了很極端的執行計畫,往後的查詢就會有嚴重的效能問題。

下一篇會介紹如何解決極端的資料分佈時,造成的不當Parameter sniffing。

沒有留言:

張貼留言