当前位置: 首页 > 编程笔记 >

何谓SQLSERVER参数嗅探问题

云卓
2023-03-14
本文向大家介绍何谓SQLSERVER参数嗅探问题,包括了何谓SQLSERVER参数嗅探问题的使用技巧和注意事项,需要的朋友参考一下

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

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

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

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

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

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

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

下面只需要用到三张表,表里面有索引:

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

数据库下载链接:AdventureWorks

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

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

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

存储过程一:

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
--发生编译,插入一个使用nested loops联接的执行计划
GO

EXEC [dbo].[Sniff] @i = 75124 -- int
--发生执行计划重用,重用上面的nested loops的执行计划
GO

测试二:

--测试二:

USE [AdventureWorks]
GO
DBCC freeproccache
GO
SET STATISTICS PROFILE ON
EXEC [dbo].[Sniff] @i = 75124 -- int
--发生编译,插入一个使用hash match联接的执行计划
GO

EXEC [dbo].[Sniff] @i = 50000 -- int
--发生执行计划重用,重用上面的hash match的执行计划
GO

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

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

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

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

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

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

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

本地变量的影响

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

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

--第一次
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

看他们的执行计划:

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

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

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

参数嗅探的解决办法

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

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

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

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

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

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

--例如前面的存储过程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,指定执行计划

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

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

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

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

可以用下面的方法,在原来那个有参数嗅探问题的存储过程“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,他还可以使用在一般的语句调优里

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

总结

以上所述是小编给大家介绍的何谓SQLSERVER参数嗅探问题,希望对大家有所帮助!

 类似资料:
  • Cooja中的错误 我正在使用Contiki ng和示例udp服务器和udp客户端。我想做几件事:1-我希望客户端节点嗅探数据包,然后一旦嗅探到数据包,就向服务器发送数据包。我成功地做到了这一点,但有一些事情我不明白:a-当我在udp客户端中启动嗅探时,通过向代码中添加以下位: 这似乎只捕获udp客户端应用程序级别的数据包,当我增加QUEUEBUF\u CONF\u NUM以允许服务器接收这些数据

  • 7.2. 密码嗅探 尽管攻击者通过嗅探(察看)你的用户和应用间的网络通信并不专门用于访问控制,但要意识到数据暴露变得越来越重要,特别是对于验证信息。 使用SSL可以有效地防止HTTP请求和回应不被暴露。对任何使用https方案的资源的请求可以防止密码嗅探。最好的方法是一直使用SSL来发送验证信息,同时你可能还想用SSL来传送所有的包含会话标识的请求以防止会话劫持。 为防止用户验证信息不致暴露,在表

  • 问题内容: 使用Python嗅探网络数据包的最佳方法是什么? 我从几个地方听说,最好的模块是一个名为Scapy的模块,不幸的是,它使python.exe在我的系统上崩溃。我认为这只是我的安装方式的问题,除了许多其他人告诉我,它在Windows上不能很好地工作。(如果有人感兴趣,我正在运行WindowsVista,这可能会影响某些事情)。 有谁知道更好的解决方案? UPD: 阅读了告诉我要安装PyP

  • 问题内容: 我只是四处看看,以了解如何制作一个程序来监听Java中的网络流量,但是我什么也找不到。我想知道是否可以通过任何方式查看网络流量。我听说过Socket的想法,但我不知道该如何工作。因此,无论如何,只是寻找一个API或自己编写一种方法。 编辑: 我很乐意想要一个API,但我也想澄清一下使用Socket嗅探通信的方式。 问题答案: jpcap,jNetPcap-是Java中的pcap包装器项

  • 上一节(《4.1 SNIFFER(嗅探器)之数据捕获(上)》)中, 我们讲解了通过Raw Socket的方式来编写Sniffer的基本方法。 本节我们继续来编写Sniffer,只不过使用现成的库,可以大大 缩短我们的工作时间和编程难度,和上一篇文章对比就知道了。 4.1.6 使用Pypcap编写Sniffer 如果在你的电脑上找不到pypcap模块,需要手动进行安装一下。在Kali中使用下面的命令

  • 网络嗅探,是监听流经本机网卡数据包的一种技术,嗅探器就是利用这种技术进行数据捕获和分析的软件。 编写嗅探器,捕获数据是前置功能,数据分析要建立在捕获的基础上。本节就数据捕获的基本原理和编程实现做详细的阐述。 4.1.1 以太网网卡的工作模式 以太网网卡是我们日常生活中见得最多的网卡,我们的电脑通过网线或者wifi接入网络,使用的都是以太网网卡。 图2 常用的以太网卡支持以下工作模式:广播模式、多播