參數嗅探（Parameter Sniffing）（1/2)

2024-07-21 02:46:49

字體：大中小

來源：轉載

供稿：網友

這個問題會在參數話的SQL語句（例如存儲過程）與SQL Server里的計劃緩存機制結合的時候會出現。這個文章分為2個部分，第1部分會介紹下參數嗅探（Parameter Sniffing）的概況，第2部分我們介紹下如何解決這個問題。

什么是參數嗅探（Parameter Sniffing）

在SQL Server里當你執行參數話的SQL查詢時，查詢優化器會基于第一個提供的參數值編譯執行計劃。然后生成的執行計劃在計劃緩存里緩存作為后期的重用。這就是說SQL Server后續會直接重用這個計劃，而不管每次你提供的不同參數值。我們需要識別2類參數值：

參數編譯值（Compile time values）
參數運行值（Runtime values）

參數編譯值是用于查詢優化器生成物理執行計劃的值。參數運行值是提供給執行計劃運行的值。對于第一次執行這些值是一致的，但接下來的執行，這些值就很可能不同了。這就會帶來嚴重的性能問題，因為執行計劃只為編譯值而優化的，不是為你接下來提供的不同運行值而優化。

如果你在第一次查詢執行的時候提供了一個特定值，然后查詢優化器選擇了非聚集索引查找和書簽查找運算符從你表里來獲取所有查詢列。這樣的執行計劃只對特定值有意義，非特定值的話，你的邏輯讀數就會很高，SQL Server會選擇全表掃描，忽略定義的非聚集索引。SQL Server選擇這2個計劃的決定點就是所謂的臨界點（Tipping Point） 。

如果書簽查找的計劃被緩存，SQL Server就不會理會輸入值，盲目重用緩存的計劃。這個情況下SQL Server的保護機制就失效了，只從計劃緩存里執行緩存的計劃。作為副作用，你的IO成本（邏輯都）就會爆表，查詢的性能就會非常糟糕。我們來演示下這個情況，下面的腳本會創建一個簡單的表，在表的第2列有不平均的數據分布（就第1條值是1，剩下的1499條值都是2）。

 1 -- Create a test table 2 CREATE TABLE Table1 3 ( 4     Column1 INT IDENTITY, 5     Column2 INT 6 ) 7 GO 8  9 CREATE NONCLUSTERED INDEX idx_Test ON Table1(Column2)10 11 -- Insert 1500 records into Table112 INSERT INTO Table1 (Column2) VALUES (1)13 14 SELECT TOP 1499 IDENTITY(INT, 1, 1) AS n INTO #Nums15 FROM16 master.dbo.syscolumns sc117 18 INSERT INTO Table1 (Column2)19 SELECT 2 FROM #nums20 DROP TABLE #nums21 GO

基于這個不平均的數據分布和臨界點，對于同個邏輯查詢會有2個不同的執行計劃，點擊工具欄的顯示包含實際的執行計劃：

1 SELECT * FROM dbo.Table1 WHERE Column2=12 SELECT * FROM dbo.Table1 WHERE Column2=2

現在當你創建一個存儲過程時，查詢優化器會根據第一次提供的參數值生成執行計劃，然后在接下來的執行中就會盲目重用了。

1 -- Create a new stored PRocedure for data retrieval2 CREATE PROCEDURE RetrieveData3 (4     @Col2Value INT5 )6 AS7     SELECT * FROM Table18     WHERE Column2 = @Col2Value9 GO

1 SET STATISTICS IO ON 2 EXEC dbo.RetrieveData @Col2Value = 1 -- int3 EXEC dbo.RetrieveData @Col2Value = 2 -- int

現在當你用1值運行存儲過程時，只返回1條記錄，查詢優化器在執行計劃里選擇書簽查找。查詢只產生3個邏輯讀。但是當你用2值運行存儲過程時，緩存的計劃被重用，書簽查找反復執行1499次。每條記錄上都執行！查詢現在產生了1505個邏輯讀。這和剛才的執行完全不同。當你看查看2值里執行計劃里，SELECT運算符的屬性時，在參數列表里你可以看到：