共計 4377 個字符，預計需要花費 11 分鐘才能閱讀完成。

丸趣 TV 小編給大家分享一下 SQLSERVER 參數嗅探問題的示例分析，相信大部分人都還不怎么了解，因此分享這篇文章給大家參考一下，希望大家閱讀完這篇文章后大有收獲，下面讓我們一起去了解一下吧！

下面測試數據庫的備份文件，里面有一些表和一些測試數據，因為我下面用的測試表都是這個數據庫里的

只需要還原數據庫就可以了，這個數據庫是 SQL2005 版本的，數據庫名：AdventureWorks

下面只需要用到三張表，表里面有索引：

[Production].[Product] [SalesOrderHeader_test] [SalesOrderDetail_test]

數據庫下載鏈接：AdventureWorks

其實簡單來講，參數嗅探我的很通俗的解釋就是：SQLSERVER 用鼻子嗅不到具體參數是多少

所以他不能選擇最合適的執行計劃去執行你的查詢，所以參數嗅探是一個不好的現象。

想真正了解參數嗅探，大家可以先創建下面兩個存儲過程

存儲過程一：

USE [AdventureWorks]
DROP PROC Sniff
CREATE PROC Sniff(@i INT)
SELECT COUNT(b.[SalesOrderID]),SUM(p.[Weight])
FROM [dbo].[SalesOrderHeader_test] a
INNER JOIN [dbo].[SalesOrderDetail_test] b
ON a.[SalesOrderID]=b.[SalesOrderID]
INNER JOIN [Production].[Product] p
ON b.[ProductID]=p.[ProductID]
WHERE a.[SalesOrderID]=@i
GO

存儲過程二：

 復制代碼   代碼如下:1 USE [AdventureWorks] 2 GO 3 DROP PROC Sniff2 4 GO 5 CREATE PROC Sniff2(@i INT) 6 AS 7 DECLARE @j INT 8 SET @j=@i 9 SELECT COUNT(b.[SalesOrderID]),SUM(p.[Weight])10 FROM [dbo].[SalesOrderHeader_test] a11 INNER JOIN [dbo].[SalesOrderDetail_test] b12 ON a.[SalesOrderID]=b.[SalesOrderID]13 INNER JOIN [Production].[Product] p14 ON b.[ProductID]=p.[ProductID]15 WHERE a.[SalesOrderID]=@j16 GO

然后請做下面這兩個測試

測試一：

-- 測試一：USE [AdventureWorks]
DBCC freeproccache
EXEC [dbo].[Sniff] @i = 500000 -- int
-- 發生編譯，插入一個使用 nested loops 聯接的執行計劃
EXEC [dbo].[Sniff] @i = 75124 -- int
-- 發生執行計劃重用，重用上面的 nested loops 的執行計劃
GO

測試二：

-- 測試二：USE [AdventureWorks]
DBCC freeproccache
SET STATISTICS PROFILE ON
EXEC [dbo].[Sniff] @i = 75124 -- int
-- 發生編譯，插入一個使用 hash match 聯接的執行計劃
EXEC [dbo].[Sniff] @i = 50000 -- int
-- 發生執行計劃重用，重用上面的 hash match 的執行計劃
GO

從上面兩個測試可以清楚地看到執行計劃重用的副作用。

由于數據分布差別很大參數 50000 和 75124 只對自己生成的執行計劃有好的性能，

如果使用對方生成的執行計劃，性能就會下降。參數 50000 返回的結果集比較小，

所以性能下降不太嚴重。參數 75124 返回的結果集大，就有了明顯的性能下降，兩個執行計劃的差別有近 10 倍

對于這種因為重用他人生成的執行計劃而導致的水土不服現象，SQSERVERL 有一個專有名詞，叫“參數嗅探 parameter sniffing”

因為語句的執行計劃對變量的值很敏感，而導致重用執行計劃會遇到性能問題，就是我上面說的

“

SQLSERVER 用鼻子嗅不到具體參數是多少，所以他不能選擇最合適的執行計劃去執行你的查詢

”

本地變量的影響

那對于有 parameter sniffing 問題的存儲過程，如果使用本地變量，會怎樣呢？

下面請看測試 3。這次用不同的變量值時，都清空執行計劃緩存，迫使其重編譯

-- 第一次
USE [AdventureWorks]
DBCC freeproccache
SET STATISTICS TIME ON
SET STATISTICS PROFILE ON
EXEC [dbo].[Sniff] @i = 50000 -- int
GO

-- 第二次
USE [AdventureWorks]
DBCC freeproccache
SET STATISTICS TIME ON
SET STATISTICS PROFILE ON
EXEC [dbo].[Sniff] @i = 75124 -- int
GO

-- 第三次
USE [AdventureWorks]
DBCC freeproccache
SET STATISTICS TIME ON
SET STATISTICS PROFILE ON
EXEC [dbo].[Sniff2] @i = 50000 -- int
GO

-- 第四次
USE [AdventureWorks]
DBCC freeproccache
SET STATISTICS TIME ON
SET STATISTICS PROFILE ON
EXEC [dbo].[Sniff2] @i = 75124 -- int
GO

看他們的執行計劃：

對于第一句和第二句，因為 SQL 在編譯的時候知道變量的值，所以在做 EstimateRows 的時候，做得非常準確，選擇了最適合他們的執行計劃

但是對于第三句和第四句，SQLSERVER 不知道 @j 的值是多少，所以在做 EstimateRows 的時候，不管代入的 @i 值是多少，

一律給 @j 一樣的預測結果。所以兩個執行計劃是完全一樣的（都是 Hash Match）。

參數嗅探的解決辦法

參數嗅探的問題發生的頻率并不高，他只會發生在一些表格里的數據分布很不均勻，或者用戶帶入的參數值很不均勻的情況下。

由于篇幅原因我就不具體說了, 只是做一些歸納

（1）用 exec() 的方式運行動態 SQL

如果在存儲過程里不是直接運行語句，而是把語句帶上變量，生成一個字符串，再讓 exec() 這樣的命令做動態語句運行，

那 SQL 就會在運行到這句話的時候，對動態語句進行編譯。

這時 SQL 已經知道了變量的值，會根據生成優化的執行計劃，從而繞過參數嗅探問題

-- 例如前面的存儲過程 Sniff，就可以改成這樣
USE [AdventureWorks]
DROP PROC NOSniff
CREATE PROC NOSniff(@i INT)
DECLARE @cmd VARCHAR(1000)
SET @cmd= SELECT COUNT(b.[SalesOrderID]),SUM(p.[Weight])
FROM [dbo].[SalesOrderHeader_test] a
INNER JOIN [dbo].[SalesOrderDetail_test] b
ON a.[SalesOrderID]=b.[SalesOrderID]
INNER JOIN [Production].[Product] p
ON b.[ProductID]=p.[ProductID]
WHERE a.[SalesOrderID]= 
EXEC(@cmd+@i)
GO

（2）使用本地變量 local variable

（3）在語句里使用 query hint，指定執行計劃

在 select,insert,update,delete 語句的最后，可以加一個 option(query_hint) 的子句

對 SQLSERVER 將要生成的執行計劃進行指導。當 DBA 知道問題所在以后，可以通過加 hint 的方式，引導

SQL 生成一個比較安全的，對所有可能的變量值都不差的執行計劃

USE [AdventureWorks]
DROP PROC NoSniff_QueryHint_Recompile
CREATE PROC NoSniff_QueryHint_Recompile(@i INT) 
SELECT COUNT(b.[SalesOrderID]),SUM(p.[Weight])
FROM [dbo].[SalesOrderHeader_test] a
INNER JOIN [dbo].[SalesOrderDetail_test] b
ON a.[SalesOrderID]=b.[SalesOrderID]
INNER JOIN [Production].[Product] p
ON b.[ProductID]=p.[ProductID]
WHERE a.[SalesOrderID]=@i
OPTION(RECOMPILE)
GO

（4）Plan Guide

可以用下面的方法，在原來那個有參數嗅探問題的存儲過程“Sniff”上，解決 sniffing 問題

USE [AdventureWorks]
EXEC [sys].[sp_create_plan_guide]
@name=N Guide1 ,
@stmt=N SELECT COUNT(b.[SalesOrderID]),SUM(p.[Weight])
FROM [dbo].[SalesOrderHeader_test] a
INNER JOIN [dbo].[SalesOrderDetail_test] b
ON a.[SalesOrderID]=b.[SalesOrderID]
INNER JOIN [Production].[Product] p
ON b.[ProductID]=p.[ProductID]
WHERE a.[SalesOrderID]=@i ,
@type=N OBJECT ,
@module_or_batch=N Sniff ,
@params=NULL,
@hints=N option(optimize for(@i=75124)) 
GO

以上是“SQLSERVER 參數嗅探問題的示例分析”這篇文章的所有內容，感謝各位的閱讀！相信大家都有了一定的了解，希望分享的內容對大家有所幫助，如果還想學習更多知識，歡迎關注丸趣 TV 行業資訊頻道！