腳本之家服務器常用軟件

快捷導航

何謂SQLSERVER參數(shù)嗅探問題

更新時間：2020年02月11日 13:49:34 作者：樺仔

這篇文章主要介紹了何謂SQLSERVER參數(shù)嗅探的相關問題，本文通過數(shù)據(jù)庫的實際案例操作給大家介紹的非常詳細，具有一定的參考借鑒價值,需要的朋友可以參考下

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

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

相信大家有泡SQLSERVER論壇的話不多不少應該都會見過“參數(shù)嗅探”這幾個字

這里有三篇帖子都是講述參數(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

下面我給出一個測試數(shù)據(jù)庫的備份文件，里面有一些表和一些測試數(shù)據(jù) ，大家可以去下載，因為我下面用的測試表都是這個數(shù)據(jù)庫里的

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

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

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

數(shù)據(jù)庫下載鏈接：AdventureWorks

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

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

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

存儲過程一：

USE [AdventureWorks]
GO
DROP PROC Sniff
GO
CREATE PROC Sniff(@i INT)
AS
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]
GO
DBCC freeproccache
GO
EXEC [dbo].[Sniff] @i = 500000 -- int
--發(fā)生編譯，插入一個使用nested loops聯(lián)接的執(zhí)行計劃
GO

EXEC [dbo].[Sniff] @i = 75124 -- int
--發(fā)生執(zhí)行計劃重用，重用上面的nested loops的執(zhí)行計劃
GO

測試二：

--測試二：

USE [AdventureWorks]
GO
DBCC freeproccache
GO
SET STATISTICS PROFILE ON
EXEC [dbo].[Sniff] @i = 75124 -- int
--發(fā)生編譯，插入一個使用hash match聯(lián)接的執(zhí)行計劃
GO

EXEC [dbo].[Sniff] @i = 50000 -- int
--發(fā)生執(zhí)行計劃重用，重用上面的hash match的執(zhí)行計劃
GO

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

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

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

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

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

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

“

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

”

本地變量的影響

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

--例如前面的存儲過程Sniff，就可以改成這樣
USE [AdventureWorks]
GO
DROP PROC NOSniff
GO
CREATE PROC NOSniff(@i INT)
AS
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，指定執(zhí)行計劃

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

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

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

USE [AdventureWorks]
GO
DROP PROC NoSniff_QueryHint_Recompile
GO
CREATE PROC NoSniff_QueryHint_Recompile(@i INT) 
AS
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

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

USE [AdventureWorks]
GO
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

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

終于搞定了，因為要搞測試數(shù)據(jù)的原因所以搞了很久啊~~

總結

以上所述是小編給大家介紹的何謂SQLSERVER參數(shù)嗅探問題，希望對大家有所幫助！

您可能感興趣的文章: