大家聽到“嗅探”這個詞應該會覺得跟黑客肯定有關系吧，使用工具嗅探一下參數，然后截獲，脫褲o(∩_∩)o 。
事實上，我覺得大家太敏感了，其實這篇文章跟數據庫安全沒有什么關系，實際上跟數據庫性能調優有關
相信大家有泡SQLSERVER論壇的話不多不少應該都會見過“參數嗅探”這幾個字
這里有三篇帖子都是講述參數嗅探的
http://msdn.microsoft.com/zh-cn/magazine/ee236412.aspx
下面我給出一個測試數據庫的備份文件，里面有一些表和一些測試數據 ，大家可以去下載，因為我下面用的測試表都是這個數據庫里的
只需要還原數據庫就可以了，這個數據庫是SQL2005版本的，數據庫名：AdventureWorks
下面只需要用到三張表，表里面有索引：
[Production].[Product] [SalesOrderHeader_test] [SalesOrderDetail_test]
數據庫下載鏈接：AdventureWorks
其實簡單來講，參數嗅探我的很通俗的解釋就是：SQLSERVER用鼻子嗅不到具體參數是多少
所以他不能選擇最合適的執行計劃去執行你的查詢，所以參數嗅探是一個不好的現象。
想真正了解參數嗅探，大家可以先創建下面兩個存儲過程
存儲過程一：
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
|
USE [AdventureWorks]GODROP PROC SniffGOCREATE PROC Sniff(@i INT)ASSELECT COUNT(b.[SalesOrderID]),SUM(p.[Weight])FROM [dbo].[SalesOrderHeader_test] aINNER JOIN [dbo].[SalesOrderDetail_test] bON a.[SalesOrderID]=b.[SalesOrderID]INNER JOIN [Production].[Product] pON b.[ProductID]=p.[ProductID]WHERE a.[SalesOrderID]=@iGO |
存儲過程二：
|
1
|
<div class="codetitle"><span><a data="85084" class="copybut" id="copybut85084"><u>復制代碼</u></a></span> 代碼如下:</div><div class="codebody" id="code85084"> 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</div> |
然后請做下面這兩個測試
測試一：
|
1
2
3
4
5
6
7
8
9
10
11
12
|
--測試一：USE [AdventureWorks]GODBCC freeproccacheGOEXEC [dbo].[Sniff] @i = 500000 -- int--發生編譯，插入一個使用nested loops聯接的執行計劃GOEXEC [dbo].[Sniff] @i = 75124 -- int--發生執行計劃重用，重用上面的nested loops的執行計劃GO |
測試二：
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
|
--測試二：USE [AdventureWorks]GODBCC freeproccacheGOSET STATISTICS PROFILE ONEXEC [dbo].[Sniff] @i = 75124 -- int--發生編譯，插入一個使用hash match聯接的執行計劃GOEXEC [dbo].[Sniff] @i = 50000 -- int--發生執行計劃重用，重用上面的hash match的執行計劃GO |
從上面兩個測試可以清楚地看到執行計劃重用的副作用。
由于數據分布差別很大參數50000和75124只對自己生成的執行計劃有好的性能，
如果使用對方生成的執行計劃，性能就會下降。參數50000返回的結果集比較小，
所以性能下降不太嚴重。參數75124返回的結果集大，就有了明顯的性能下降，兩個執行計劃的差別有近10倍
對于這種因為重用他人生成的執行計劃而導致的水土不服現象，SQSERVERL有一個專有名詞，叫“參數嗅探 parameter sniffing”
因為語句的執行計劃對變量的值很敏感，而導致重用執行計劃會遇到性能問題，就是我上面說的“
SQLSERVER用鼻子嗅不到具體參數是多少，所以他不能選擇最合適的執行計劃去執行你的查詢”
本地變量的影響
那對于有parameter sniffing問題的存儲過程，如果使用本地變量，會怎樣呢？
下面請看測試3。這次用不同的變量值時，都清空執行計劃緩存，迫使其重編譯
|
1
2
3
4
5
6
7
8
9
|
--第一次USE [AdventureWorks]GODBCC freeproccacheGOSET STATISTICS TIME ONSET STATISTICS PROFILE ONEXEC [dbo].[Sniff] @i = 50000 -- intGO |
|
1
2
3
4
5
6
7
8
9
|
--第二次USE [AdventureWorks]GODBCC freeproccacheGOSET STATISTICS TIME ONSET STATISTICS PROFILE ONEXEC [dbo].[Sniff] @i = 75124 -- intGO |
|
1
2
3
4
5
6
7
8
9
|
--第三次USE [AdventureWorks]GODBCC freeproccacheGOSET STATISTICS TIME ONSET STATISTICS PROFILE ONEXEC [dbo].[Sniff2] @i = 50000 -- intGO |
|
1
2
3
4
5
6
7
8
9
|
--第四次USE [AdventureWorks]GODBCC freeproccacheGOSET STATISTICS TIME ONSET STATISTICS PROFILE ONEXEC [dbo].[Sniff2] @i = 75124 -- intGO |
看他們的執行計劃：
對于第一句和第二句，因為SQL在編譯的時候知道變量的值，所以在做EstimateRows的時候，做得非常準確，選擇了最適合他們的執行計劃
但是對于第三句和第四句，SQLSERVER不知道@j的值是多少，所以在做EstimateRows的時候，不管代入的@i值是多少，
一律給@j一樣的預測結果。所以兩個執行計劃是完全一樣的（都是Hash Match）。
參數嗅探的解決辦法
參數嗅探的問題發生的頻率并不高，他只會發生在一些表格里的數據分布很不均勻，或者用戶帶入的參數值很不均勻的情況下。
由于篇幅原因我就不具體說了,只是做一些歸納
（1）用exec()的方式運行動態SQL
如果在存儲過程里不是直接運行語句，而是把語句帶上變量，生成一個字符串，再讓exec()這樣的命令做動態語句運行，
那SQL就會在運行到這句話的時候，對動態語句進行編譯。
這時SQL已經知道了變量的值，會根據生成優化的執行計劃，從而繞過參數嗅探問題
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
|
--例如前面的存儲過程Sniff，就可以改成這樣USE [AdventureWorks]GODROP PROC NOSniffGOCREATE PROC NOSniff(@i INT)ASDECLARE @cmd VARCHAR(1000)SET @cmd='SELECT COUNT(b.[SalesOrderID]),SUM(p.[Weight])FROM [dbo].[SalesOrderHeader_test] aINNER JOIN [dbo].[SalesOrderDetail_test] bON a.[SalesOrderID]=b.[SalesOrderID]INNER JOIN [Production].[Product] pON 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生成一個比較安全的，對所有可能的變量值都不差的執行計劃
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
|
USE [AdventureWorks]GODROP PROC NoSniff_QueryHint_RecompileGOCREATE PROC NoSniff_QueryHint_Recompile(@i INT) ASSELECT COUNT(b.[SalesOrderID]),SUM(p.[Weight])FROM [dbo].[SalesOrderHeader_test] aINNER JOIN [dbo].[SalesOrderDetail_test] bON a.[SalesOrderID]=b.[SalesOrderID]INNER JOIN [Production].[Product] pON b.[ProductID]=p.[ProductID]WHERE a.[SalesOrderID]=@iOPTION(RECOMPILE)GO |
（4）Plan Guide
可以用下面的方法，在原來那個有參數嗅探問題的存儲過程“Sniff”上，解決sniffing問題
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
|
USE [AdventureWorks]GOEXEC [sys].[sp_create_plan_guide]@name=N'Guide1',@stmt=N'SELECT COUNT(b.[SalesOrderID]),SUM(p.[Weight])FROM [dbo].[SalesOrderHeader_test] aINNER JOIN [dbo].[SalesOrderDetail_test] bON a.[SalesOrderID]=b.[SalesOrderID]INNER JOIN [Production].[Product] pON 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 |
對于Plan Guide，他還可以使用在一般的語句調優里
終于搞定了，因為要搞測試數據的原因所以搞了很久啊~~
總結
以上所述是小編給大家介紹的何謂SQLSERVER參數嗅探問題，希望對大家有所幫助！
原文鏈接：https://www.cnblogs.com/lyhabc/archive/2013/03/02/2941144.html
