SQLSERVER参数嗅探,性能调优
来源:互联网 发布:数据展示平台网页 编辑:程序博客网 时间:2024/06/10 03:29
实际上跟数据库性能调优有关
这里有三篇帖子都是讲述参数嗅探的
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
下面只需要用到三张表,表里面有索引:
其实简单来讲,参数嗅探很通俗的解释就是:SQLSERVER用鼻子嗅不到具体参数是多少
所以他不能选择最合适的执行计划去执行你的查询,所以参数嗅探是一个不好的现象。
想真正了解参数嗅探,大家可以先创建下面两个存储过程
存储过程一:
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] b10 ON a.[SalesOrderID]=b.[SalesOrderID]11 INNER JOIN [Production].[Product] p12 ON b.[ProductID]=p.[ProductID]13 WHERE a.[SalesOrderID]=@i14 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
然后请做下面这两个测试
测试一:
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 -- int11 --发生执行计划重用,重用上面的nested loops的执行计划12 GO
测试二:
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 GO11 12 EXEC [dbo].[Sniff] @i = 50000 -- int13 --发生执行计划重用,重用上面的hash match的执行计划14 GO
从上面两个测试可以清楚地看到执行计划重用的副作用。
由于数据分布差别很大参数50000和75124只对自己生成的执行计划有好的性能,
如果使用对方生成的执行计划,性能就会下降。参数50000返回的结果集比较小,
所以性能下降不太严重。参数75124返回的结果集大,就有了明显的性能下降,两个执行计划的差别有近10倍
对于这种因为重用他人生成的执行计划而导致的水土不服现象,SQSERVERL有一个专有名词,叫“参数嗅探 parameter sniffing”
因为语句的执行计划对变量的值很敏感,而导致重用执行计划会遇到性能问题,就是我上面说的
“
SQLSERVER用鼻子嗅不到具体参数是多少,所以他不能选择最合适的执行计划去执行你的查询
”
本地变量的影响
那对于有parameter sniffing问题的存储过程,如果使用本地变量,会怎样呢?
下面请看测试3。这次用不同的变量值时,都清空执行计划缓存,迫使其重编译
1 --第一次2 USE [AdventureWorks]3 GO4 DBCC freeproccache5 GO6 SET STATISTICS TIME ON7 SET STATISTICS PROFILE ON8 EXEC [dbo].[Sniff] @i = 50000 -- int9 GO
1 --第二次2 USE [AdventureWorks]3 GO4 DBCC freeproccache5 GO6 SET STATISTICS TIME ON7 SET STATISTICS PROFILE ON8 EXEC [dbo].[Sniff] @i = 75124 -- int9 GO
1 --第三次2 USE [AdventureWorks]3 GO4 DBCC freeproccache5 GO6 SET STATISTICS TIME ON7 SET STATISTICS PROFILE ON8 EXEC [dbo].[Sniff2] @i = 50000 -- int9 GO
1 --第四次2 USE [AdventureWorks]3 GO4 DBCC freeproccache5 GO6 SET STATISTICS TIME ON7 SET STATISTICS PROFILE ON8 EXEC [dbo].[Sniff2] @i = 75124 -- int9 GO
看他们的执行计划:
对于第一句和第二句,因为SQL在编译的时候知道变量的值,所以在做EstimateRows的时候,做得非常准确,选择了最适合他们的执行计划
但是对于第三句和第四句,SQLSERVER不知道@j的值是多少,所以在做EstimateRows的时候,不管代入的@i值是多少,
一律给@j一样的预测结果。所以两个执行计划是完全一样的(都是Hash Match)。
参数嗅探的解决办法
参数嗅探的问题发生的频率并不高,他只会发生在一些表格里的数据分布很不均匀,或者用户带入的参数值很不均匀的情况下。
由于篇幅原因我就不具体说了,只是做一些归纳
(1)用exec()的方式运行动态SQL
如果在存储过程里不是直接运行语句,而是把语句带上变量,生成一个字符串,再让exec()这样的命令做动态语句运行,
那SQL就会在运行到这句话的时候,对动态语句进行编译。
这时SQL已经知道了变量的值,会根据生成优化的执行计划,从而绕过参数嗅探问题
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] 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]='16 EXEC(@cmd+@i)17 GO
(2)使用本地变量local variable
(3)在语句里使用query hint,指定执行计划
在select,insert,update,delete语句的最后,可以加一个"option(<query_hint>)"的子句
对SQLSERVER将要生成的执行计划进行指导。当DBA知道问题所在以后,可以通过加hint的方式,引导
SQL生成一个比较安全的,对所有可能的变量值都不差的执行计划
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] b10 ON a.[SalesOrderID]=b.[SalesOrderID]11 INNER JOIN [Production].[Product] p12 ON b.[ProductID]=p.[ProductID]13 WHERE a.[SalesOrderID]=@i14 OPTION(RECOMPILE)15 GO
(4)Plan Guide
可以用下面的方法,在原来那个有参数嗅探问题的存储过程“Sniff”上,解决sniffing问题
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] p10 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
对于Plan Guide,他还可以使用在一般的语句调优里
终于搞定了,因为要搞测试数据的原因所以搞了很久啊~~
- SQLSERVER参数嗅探,性能调优
- SQLServer性能分析参数
- sqlserver性能调优
- sqlserver性能调优(一)
- sqlserver性能调优文档
- 浅谈sqlserver性能调优
- sqlserver性能调优方法论
- sqlserver性能调优工具
- 何谓SQLSERVER参数嗅探
- sqlserver性能调优入门篇
- sqlserver性能调优方法论与常用工具
- kettle sqlserver mysql ETL性能调优
- SQLServer性能调优之缓存
- sqlserver性能性能调优实战学习总结一
- 参数优化性能调优
- Ceph 参数性能调优
- sqlserver性能监控及调优资料汇总
- Spark性能调优-性能监控、参数调整
- 计算机硬件信息查询
- UnityShader案例(七)——遮罩纹理
- dicom之将dcm文件解析为jpg后等比例压缩
- javaweb开发要学习的所有技术和框架总结:
- Hibernate--inverse属性与cascade属性
- SQLSERVER参数嗅探,性能调优
- mysql命令gruop by报错this is incompatible with sql_mode=only_full_group_by
- sleep函数的实现
- 【电路第五章】含运算放大器的电路分析方法
- unity3d官方教学示例之SpaceShooter学习笔记
- jQuery插件的几种写法大总结
- 记录NSLog要输出的格式化占位符
- onmousemove、onmouseover、 onmouseup及onclick的区别
- Linux / unbuntu 下安装python