何謂SQL Server參數(shù)嗅探

時(shí)間：2018-11-26 15:50:02

關(guān)鍵字： server sql

手機(jī)看文章

掃描二維碼
隨時(shí)隨地手機(jī)看文章

[導(dǎo)讀]大家聽到“嗅探”這個(gè)詞應(yīng)該會(huì)覺得跟黑客肯定有關(guān)系吧，使用工具嗅探一下參數(shù)，然后截獲，脫褲o(∩_∩)o 。事實(shí)上，我覺得大家太敏感了，其實(shí)這篇文章跟數(shù)據(jù)庫(kù)安全沒有什么關(guān)系，實(shí)際上跟數(shù)據(jù)庫(kù)性能調(diào)優(yōu)有關(guān)相

大家聽到“嗅探”這個(gè)詞應(yīng)該會(huì)覺得跟黑客肯定有關(guān)系吧，使用工具嗅探一下參數(shù)，然后截獲，脫褲o(∩_∩)o 。

事實(shí)上，我覺得大家太敏感了，其實(shí)這篇文章跟數(shù)據(jù)庫(kù)安全沒有什么關(guān)系，實(shí)際上跟數(shù)據(jù)庫(kù)性能調(diào)優(yōu)有關(guān)

相信大家有泡SQLSERVER論壇的話不多不少應(yīng)該都會(huì)見過(guò)“參數(shù)嗅探”這幾個(gè)字

這里有三篇帖子都是講述參數(shù)嗅探的

http://social.msdn.microsoft.com/Forums/zh-CN/sqlserverzhchs/thread/caccb7f3-8366-4954-8f8a-145eb6bca9dd

http://msdn.microsoft.com/zh-cn/magazine/ee236412.aspx

http://social.msdn.microsoft.com/Forums/zh-CN/sqlserverzhchs/thread/bfbe54de-ac00-49e9-a83b-f97a60bf74ef

?下面我給出一個(gè)測(cè)試數(shù)據(jù)庫(kù)的備份文件，里面有一些表和一些測(cè)試數(shù)據(jù) ，大家可以去下載，因?yàn)槲蚁旅嬗玫臏y(cè)試表都是這個(gè)數(shù)據(jù)庫(kù)里的

只需要還原數(shù)據(jù)庫(kù)就可以了，這個(gè)數(shù)據(jù)庫(kù)是SQL2005版本的，數(shù)據(jù)庫(kù)名：AdventureWorks

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

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

數(shù)據(jù)庫(kù)下載鏈接：AdventureWorks_Full_backup_2013-3-4.bak

其實(shí)簡(jiǎn)單來(lái)講，參數(shù)嗅探我的很通俗的解釋就是：SQLSERVER用鼻子嗅不到具體參數(shù)是多少

所以他不能選擇最合適的執(zhí)行計(jì)劃去執(zhí)行你的查詢，所以參數(shù)嗅探是一個(gè)不好的現(xiàn)象。

想真正了解參數(shù)嗅探，大家可以先創(chuàng)建下面兩個(gè)存儲(chǔ)過(guò)程

存儲(chǔ)過(guò)程一：

?1?USE?[AdventureWorks]
?2?GO
?3?DROP?PROC?Sniff
?4?GO
?5?CREATE?PROC?Sniff(@i?INT)
?6?AS
?7?SELECT?COUNT(b.[SalesOrderID]),SUM(p.[Weight])
?8?FROM?[dbo].[SalesOrderHeader_test]?a
?9?INNER?JOIN?[dbo].[SalesOrderDetail_test]?b
10?ON?a.[SalesOrderID]=b.[SalesOrderID]
11?INNER?JOIN?[Production].[Product]?p
12?ON?b.[ProductID]=p.[ProductID]
13?WHERE?a.[SalesOrderID]=@i
14?GO

存儲(chǔ)過(guò)程二：

?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]?a
11?INNER?JOIN?[dbo].[SalesOrderDetail_test]?b
12?ON?a.[SalesOrderID]=b.[SalesOrderID]
13?INNER?JOIN?[Production].[Product]?p
14?ON?b.[ProductID]=p.[ProductID]
15?WHERE?a.[SalesOrderID]=@j
16?GO

然后請(qǐng)做下面這兩個(gè)測(cè)試

測(cè)試一：

?1?--測(cè)試一：
?2?USE?[AdventureWorks]
?3?GO
?4?DBCC?freeproccache
?5?GO
?6?EXEC?[dbo].[Sniff]?@i?=?500000?--?int
?7?--發(fā)生編譯，插入一個(gè)使用nested?loops聯(lián)接的執(zhí)行計(jì)劃
?8?GO
?9?
10?EXEC?[dbo].[Sniff]?@i?=?75124?--?int
11?--發(fā)生執(zhí)行計(jì)劃重用，重用上面的nested?loops的執(zhí)行計(jì)劃
12?GO

測(cè)試二：

?1?--測(cè)試二：
?2?
?3?USE?[AdventureWorks]
?4?GO
?5?DBCC?freeproccache
?6?GO
?7?SET?STATISTICS?PROFILE?ON
?8?EXEC?[dbo].[Sniff]?@i?=?75124?--?int
?9?--發(fā)生編譯，插入一個(gè)使用hash?match聯(lián)接的執(zhí)行計(jì)劃
10?GO
11?
12?EXEC?[dbo].[Sniff]?@i?=?50000?--?int
13?--發(fā)生執(zhí)行計(jì)劃重用，重用上面的hash?match的執(zhí)行計(jì)劃
14?GO

?從上面兩個(gè)測(cè)試可以清楚地看到執(zhí)行計(jì)劃重用的副作用。

由于數(shù)據(jù)分布差別很大參數(shù)50000和75124只對(duì)自己生成的執(zhí)行計(jì)劃有好的性能，

如果使用對(duì)方生成的執(zhí)行計(jì)劃，性能就會(huì)下降。參數(shù)50000返回的結(jié)果集比較小，

所以性能下降不太嚴(yán)重。參數(shù)75124返回的結(jié)果集大，就有了明顯的性能下降，兩個(gè)執(zhí)行計(jì)劃的差別有近10倍

對(duì)于這種因?yàn)橹赜盟松傻膱?zhí)行計(jì)劃而導(dǎo)致的水土不服現(xiàn)象，SQSERVERL有一個(gè)專有名詞，叫“參數(shù)嗅探 parameter sniffing”

因?yàn)檎Z(yǔ)句的執(zhí)行計(jì)劃對(duì)變量的值很敏感，而導(dǎo)致重用執(zhí)行計(jì)劃會(huì)遇到性能問(wèn)題，就是我上面說(shuō)的

“

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

”

?本地變量的影響

那對(duì)于有parameter sniffing問(wèn)題的存儲(chǔ)過(guò)程，如果使用本地變量，會(huì)怎樣呢？

下面請(qǐng)看測(cè)試3。這次用不同的變量值時(shí)，都清空?qǐng)?zhí)行計(jì)劃緩存，迫使其重編譯

1?--第一次
2?USE?[AdventureWorks]
3?GO
4?DBCC?freeproccache
5?GO
6?SET?STATISTICS?TIME?ON
7?SET?STATISTICS?PROFILE?ON
8?EXEC?[dbo].[Sniff]?@i?=?50000?--?int
9?GO

1?--第二次
2?USE?[AdventureWorks]
3?GO
4?DBCC?freeproccache
5?GO
6?SET?STATISTICS?TIME?ON
7?SET?STATISTICS?PROFILE?ON
8?EXEC?[dbo].[Sniff]?@i?=?75124?--?int
9?GO

1?--第三次
2?USE?[AdventureWorks]
3?GO
4?DBCC?freeproccache
5?GO
6?SET?STATISTICS?TIME?ON
7?SET?STATISTICS?PROFILE?ON
8?EXEC?[dbo].[Sniff2]?@i?=?50000?--?int
9?GO

1?--第四次
2?USE?[AdventureWorks]
3?GO
4?DBCC?freeproccache
5?GO
6?SET?STATISTICS?TIME?ON
7?SET?STATISTICS?PROFILE?ON
8?EXEC?[dbo].[Sniff2]?@i?=?75124?--?int
9?GO

?看他們的執(zhí)行計(jì)劃：

對(duì)于第一句和第二句，因?yàn)镾QL在編譯的時(shí)候知道變量的值，所以在做EstimateRows的時(shí)候，做得非常準(zhǔn)確，選擇了最適合他們的執(zhí)行計(jì)劃

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

一律給@j一樣的預(yù)測(cè)結(jié)果。所以兩個(gè)執(zhí)行計(jì)劃是完全一樣的（都是Hash Match）。

參數(shù)嗅探的解決辦法

?參數(shù)嗅探的問(wèn)題發(fā)生的頻率并不高，他只會(huì)發(fā)生在一些表格里的數(shù)據(jù)分布很不均勻，或者用戶帶入的參數(shù)值很不均勻的情況下。

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

（1）用exec()的方式運(yùn)行動(dòng)態(tài)SQL

如果在存儲(chǔ)過(guò)程里不是直接運(yùn)行語(yǔ)句，而是把語(yǔ)句帶上變量，生成一個(gè)字符串，再讓exec()這樣的命令做動(dòng)態(tài)語(yǔ)句運(yùn)行，

那SQL就會(huì)在運(yùn)行到這句話的時(shí)候，對(duì)動(dòng)態(tài)語(yǔ)句進(jìn)行編譯。

這時(shí)SQL已經(jīng)知道了變量的值，會(huì)根據(jù)生成優(yōu)化的執(zhí)行計(jì)劃，從而繞過(guò)參數(shù)嗅探問(wèn)題

?1?--例如前面的存儲(chǔ)過(guò)程Sniff，就可以改成這樣
?2?USE?[AdventureWorks]
?3?GO
?4?DROP?PROC?NOSniff
?5?GO
?6?CREATE?PROC?NOSniff(@i?INT)
?7?AS
?8?DECLARE?@cmd?VARCHAR(1000)
?9?SET?@cmd='SELECT?COUNT(b.[SalesOrderID]),SUM(p.[Weight])
10?FROM?[dbo].[SalesOrderHeader_test]?a
11?INNER?JOIN?[dbo].[SalesOrderDetail_test]?b
12?ON?a.[SalesOrderID]=b.[SalesOrderID]
13?INNER?JOIN?[Production].[Product]?p
14?ON?b.[ProductID]=p.[ProductID]
15?WHERE?a.[SalesOrderID]='
16?EXEC(@cmd+@i)
17?GO

（2）使用本地變量local variable

（3）在語(yǔ)句里使用query hint，指定執(zhí)行計(jì)劃

在select,insert,update,delete語(yǔ)句的最后，可以加一個(gè)"option(

對(duì)SQLSERVER將要生成的執(zhí)行計(jì)劃進(jìn)行指導(dǎo)。當(dāng)DBA知道問(wèn)題所在以后，可以通過(guò)加hint的方式，引導(dǎo)

SQL生成一個(gè)比較安全的，對(duì)所有可能的變量值都不差的執(zhí)行計(jì)劃

?1?USE?[AdventureWorks]
?2?GO
?3?DROP?PROC?NoSniff_QueryHint_Recompile
?4?GO
?5?CREATE?PROC?NoSniff_QueryHint_Recompile(@i?INT)?
?6?AS
?7?SELECT?COUNT(b.[SalesOrderID]),SUM(p.[Weight])
?8?FROM?[dbo].[SalesOrderHeader_test]?a
?9?INNER?JOIN?[dbo].[SalesOrderDetail_test]?b
10?ON?a.[SalesOrderID]=b.[SalesOrderID]
11?INNER?JOIN?[Production].[Product]?p
12?ON?b.[ProductID]=p.[ProductID]
13?WHERE?a.[SalesOrderID]=@i
14?OPTION(RECOMPILE)
15?GO

（4）Plan Guide

?可以用下面的方法，在原來(lái)那個(gè)有參數(shù)嗅探問(wèn)題的存儲(chǔ)過(guò)程“Sniff”上，解決sniffing問(wèn)題

?1?USE?[AdventureWorks]
?2?GO
?3?EXEC?[sys].[sp_create_plan_guide]
?4?@name=N'Guide1',
?5?@stmt=N'SELECT?COUNT(b.[SalesOrderID]),SUM(p.[Weight])
?6?FROM?[dbo].[SalesOrderHeader_test]?a
?7?INNER?JOIN?[dbo].[SalesOrderDetail_test]?b
?8?ON?a.[SalesOrderID]=b.[SalesOrderID]
?9?INNER?JOIN?[Production].[Product]?p
10?ON?b.[ProductID]=p.[ProductID]
11?WHERE?a.[SalesOrderID]=@i',
12?@type=N'OBJECT',
13?@module_or_batch=N'Sniff',
14?@params=NULL,
15?@hints=N'option(optimize?for(@i=75124))';
16?GO

對(duì)于Plan Guide，他還可以使用在一般的語(yǔ)句調(diào)優(yōu)里

轉(zhuǎn)自：https://www.cnblogs.com/lyhabc/archive/2013/03/02/2941144.html