[SQL Server] 淺談Parameter sniffing (一)

什麼是Parameter sniffing?

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

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

我們來簡單做個模擬：

先建立一個Orders的Table，並在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

我們刻意模擬極端的資料分布，打10000筆CustomerID = 1的資料以及10筆CustomerID = 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 procedure的Plan，就重複使用了這個執行計畫所規劃的演算法來執行所有查詢。

原本Parameter sniffing是一番美意，幫助我們重覆使用執行計畫，避免Memory大量產生重覆的執行計畫，而且每次執行Stored Procedure都要花費額外的效能來根據參數編譯。

但在這種資料分佈很極端的情況，如果第一次查詢選擇了很極端的執行計畫，往後的查詢就會有嚴重的效能問題。

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