何谓SQLSERVER参数嗅探

偏执的太偏执、 2022-06-07 04:51 262阅读 0赞

http://www.cnblogs.com/lyhabc/archive/2013/03/02/2941144.html

大家听到“嗅探”这个词应该会觉得跟黑客肯定有关系吧，使用工具嗅探一下参数，然后截获，脱裤o(∩\_∩)o 。

事实上，我觉得大家太敏感了，其实这篇文章跟数据库安全没有什么关系，实际上跟数据库性能调优有关

相信大家有泡SQLSERVER论坛的话不多不少应该都会见过“参数嗅探”这几个字

这里有三篇帖子都是讲述参数嗅探的

[http://social.msdn.microsoft.com/Forums/zh-CN/sqlserverzhchs/thread/caccb7f3-8366-4954-8f8a-145eb6bca9dd][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_msdn.microsoft.com_zh-cn_magazine_ee236412.aspx]

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

--------------------

下面我给出一个测试数据库的备份文件，里面有一些表和一些测试数据 ，大家可以去下载，因为我下面用的测试表都是这个数据库里的

只需要还原数据库就可以了，这个数据库是SQL2005版本的，数据库名：AdventureWorks

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

\[Production\].\[Product\]  
\[SalesOrderHeader\_test\]  
\[SalesOrderDetail\_test\]

数据库下载链接：[AdventureWorks\_Full\_backup\_2013-3-4.bak][AdventureWorks_Full_backup_2013-3-4.bak][  
][Link 1]

![04220643-1b72c298863a4005892e2fbb1e98ca80.jpg][]

其实简单来讲，参数嗅探我的很通俗的解释就是：SQLSERVER用鼻子嗅不到具体参数是多少

所以他不能选择最合适的执行计划去执行你的查询，所以参数嗅探是一个不好的现象。

--------------------

想真正了解参数嗅探，大家可以先创建下面两个存储过程

存储过程一：

![复制代码][copycode.gif]

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

![复制代码][copycode.gif]

存储过程二：

![复制代码][copycode.gif]

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

![复制代码][copycode.gif]

然后请做下面这两个测试

测试一：

![复制代码][copycode.gif]

1 --测试一：
     2 USE [AdventureWorks]
     3 GO
     4 DBCC freeproccache
     5 GO
     6 EXEC [dbo].[Sniff] @i = 500000 -- int
     7 --发生编译，插入一个使用nested loops联接的执行计划
     8 GO
     9 
    10 EXEC [dbo].[Sniff] @i = 75124 -- int
    11 --发生执行计划重用，重用上面的nested loops的执行计划
    12 GO

![复制代码][copycode.gif]

![04220746-599ed50af77a4d00b0cc238f6d4cf507.jpg][]

测试二：

![复制代码][copycode.gif]

1 --测试二：
     2 
     3 USE [AdventureWorks]
     4 GO
     5 DBCC freeproccache
     6 GO
     7 SET STATISTICS PROFILE ON
     8 EXEC [dbo].[Sniff] @i = 75124 -- int
     9 --发生编译，插入一个使用hash match联接的执行计划
    10 GO
    11 
    12 EXEC [dbo].[Sniff] @i = 50000 -- int
    13 --发生执行计划重用，重用上面的hash match的执行计划
    14 GO

![复制代码][copycode.gif]

![04221447-da07bd398eb448cdab02be25a4d8a81f.jpg][]

从上面两个测试可以清楚地看到执行计划重用的副作用。

由于数据分布差别很大参数50000和75124只对自己生成的执行计划有好的性能，

如果使用对方生成的执行计划，性能就会下降。参数50000返回的结果集比较小，

所以性能下降不太严重。参数75124返回的结果集大，就有了明显的性能下降，两个执行计划的差别有近10倍

--------------------

对于这种因为重用他人生成的执行计划而导致的水土不服现象，SQSERVERL有一个专有名词，叫“参数嗅探 parameter sniffing”

因为语句的执行计划对变量的值很敏感，而导致重用执行计划会遇到性能问题，就是我上面说的

“

SQLSERVER用鼻子嗅不到具体参数是多少，所以他不能选择最合适的执行计划去执行你的查询

”

--------------------

本地变量的影响

那对于有parameter sniffing问题的存储过程，如果使用本地变量，会怎样呢？

下面请看测试3。这次用不同的变量值时，都清空执行计划缓存，迫使其重编译

![复制代码][copycode.gif]

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

![复制代码][copycode.gif]

![04221802-eb8b120c829c49f88f7cc97313e4988c.jpg][]

![04222415-f6be883affba446ca514c87658db430f.jpg][]

![复制代码][copycode.gif]

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

![复制代码][copycode.gif]

![04221900-c0876a6afe314ca6b41ee204591275b1.jpg][]

![04222504-9320b69e151c4de3921a3aa3443bb079.jpg][]

![复制代码][copycode.gif]

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

![复制代码][copycode.gif]

![04221948-d1e41e4e06c245439fd5b5890e3e6974.jpg][]

![04222543-b381230583ca42e78e6fa983b55c7306.jpg][]

![复制代码][copycode.gif]

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

![复制代码][copycode.gif]

![04222102-750a0b5cbd634a4fba01688114f71e15.jpg][]

![04222713-22e880d9497943948960757142b924f6.jpg][]

看他们的执行计划：

对于第一句和第二句，因为SQL在编译的时候知道变量的值，所以在做EstimateRows的时候，做得非常准确，选择了最适合他们的执行计划

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

一律给@j一样的预测结果。所以两个执行计划是完全一样的（都是Hash Match）。

--------------------

参数嗅探的解决办法

参数嗅探的问题发生的频率并不高，他只会发生在一些表格里的数据分布很不均匀，或者用户带入的参数值很不均匀的情况下。

由于篇幅原因我就不具体说了,只是做一些归纳

（1）用exec()的方式运行动态SQL

如果在存储过程里不是直接运行语句，而是把语句带上变量，生成一个字符串，再让exec()这样的命令做动态语句运行，

那SQL就会在运行到这句话的时候，对动态语句进行编译。

这时SQL已经知道了变量的值，会根据生成优化的执行计划，从而绕过参数嗅探问题

![复制代码][copycode.gif]

1 --例如前面的存储过程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

![复制代码][copycode.gif]

（2）使用本地变量local variable

（3）在语句里使用query hint，指定执行计划

在select,insert,update,delete语句的最后，可以加一个"option(<query\_hint>)"的子句

对SQLSERVER将要生成的执行计划进行指导。当DBA知道问题所在以后，可以通过加hint的方式，引导

SQL生成一个比较安全的，对所有可能的变量值都不差的执行计划

![复制代码][copycode.gif]

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

![复制代码][copycode.gif]

（4）Plan Guide

可以用下面的方法，在原来那个有参数嗅探问题的存储过程“Sniff”上，解决sniffing问题

![复制代码][copycode.gif]

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

![复制代码][copycode.gif]

对于Plan Guide，他还可以使用在一般的语句调优里

终于搞定了，因为要搞测试数据的原因所以搞了很久啊~~

[http_social.msdn.microsoft.com_Forums_zh-CN_sqlserverzhchs_thread_caccb7f3-8366-4954-8f8a-145eb6bca9dd]: 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://msdn.microsoft.com/zh-cn/magazine/ee236412.aspx
[http_social.msdn.microsoft.com_Forums_zh-CN_sqlserverzhchs_thread_bfbe54de-ac00-49e9-a83b-f97a60bf74ef]: http://social.msdn.microsoft.com/Forums/zh-CN/sqlserverzhchs/thread/bfbe54de-ac00-49e9-a83b-f97a60bf74ef
[AdventureWorks_Full_backup_2013-3-4.bak]: http://www.kuaipan.cn/file/id_4401224786924633.htm
[Link 1]: http://www.kuaipan.cn/file/id_4401224786924621.htm
[04220643-1b72c298863a4005892e2fbb1e98ca80.jpg]: /images/20220607/1833a9938c6c468d806177baa591cc05.png
[copycode.gif]: /images/20220607/627c9ac5e75649debd2a2ef1c6c78eac.png
[04220746-599ed50af77a4d00b0cc238f6d4cf507.jpg]: /images/20220607/26894d2a038a40f49ae258559af713ab.png
[04221447-da07bd398eb448cdab02be25a4d8a81f.jpg]: /images/20220607/b1c4045a5798469db97dbcbc9656ac65.png
[04221802-eb8b120c829c49f88f7cc97313e4988c.jpg]: /images/20220607/05fa7195425a48b8b03da18cde37d0d7.png
[04222415-f6be883affba446ca514c87658db430f.jpg]: /images/20220607/d8ac0a380e2845b792be23f49dd97b5d.png
[04221900-c0876a6afe314ca6b41ee204591275b1.jpg]: /images/20220607/8aa52e51ad1b4722bd7335db00d1dc0b.png
[04222504-9320b69e151c4de3921a3aa3443bb079.jpg]: /images/20220607/3e2b27e5dd9b4b8ca4ec65e793eab1b8.png
[04221948-d1e41e4e06c245439fd5b5890e3e6974.jpg]: /images/20220607/7c802939875347369ac26b0bd4b52893.png
[04222543-b381230583ca42e78e6fa983b55c7306.jpg]: /images/20220607/b8c1d60a43684e2ba634e30c867ee785.png
[04222102-750a0b5cbd634a4fba01688114f71e15.jpg]: /images/20220607/95ca5a86978543b19d4450bb5c8aae09.png
[04222713-22e880d9497943948960757142b924f6.jpg]: /images/20220607/bd14e9ef072a4ce4a7e44c94e9ad0109.png