SQL Server 查询优化（测试02）参数嗅探-执行计划选择

最新推荐文章于 2022-11-20 10:20:13 发布
最新推荐文章于 2022-11-20 10:20:13 发布
阅读量3.4k 收藏 1
点赞数
分类专栏： SQLServer SQLServer 调优优化
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/kk185800961/article/details/42207943
版权 

最近常看到"参数嗅探"这个词,看了几篇文章，于是就自己摸索做个测试来加深印象!

去官网下载了数据库:AdventureWorks2012


直接测试吧!
找几个熟悉的表关联起来,用ProductID作为条件找到两个ID返回行数相差较大的值.
ProductID=870(4688行)	ProductID=897(2行)

【测试一】 

--先清空计划缓存
DBCC FREEPROCCACHE

--执行前先打开计数器监控查看(分开执行以下查询)
select sdh.SalesOrderID,sdh.SalesOrderNumber,P.ProductID,p.Name,sod.LineTotal
from [Sales].[SalesOrderHeader] sdh
inner join [Sales].[SalesOrderDetail] sod on sdh.SalesOrderID = sod.SalesOrderID
inner join [Production].[Product] p on sod.ProductID = p.ProductID
where P.ProductID =870

select sdh.SalesOrderID,sdh.SalesOrderNumber,P.ProductID,p.Name,sod.LineTotal
from [Sales].[SalesOrderHeader] sdh
inner join [Sales].[SalesOrderDetail] sod on sdh.SalesOrderID = sod.SalesOrderID
inner join [Production].[Product] p on sod.ProductID = p.ProductID
where P.ProductID =897


先看计数器,有两个绿色的峰值为1.就是上面分别执行时发生的编译次数.

--	查看缓存对象执行类型:Adhoc(即时查询)
SELECT cacheobjtype,objtype,refcounts,usecounts,[sql]
FROM sys.syscacheobjects
WHERE [sql] LIKE '%SalesOrderID%' AND [sql] NOT LIKE '%sys%'

--	再用视图查看缓存查询计划和计划大小
SELECT refcounts,usecounts,cacheobjtype,size_in_bytes,[text],query_plan
FROM sys.dm_exec_cached_plans 
CROSS APPLY sys.dm_exec_sql_text(plan_handle)
CROSS APPLY sys.dm_exec_query_plan(plan_handle)
WHERE [text] LIKE '%SalesOrderID%' AND [text] NOT LIKE '%sys%'


可以看到生成了两个不同的查询计划(query_plan),并且占用了缓存(size_in_bytes).


以上这种写法的优缺点是:
缺点: 如果查询条件值发生变化,每次都会作为新的查询语句编译第一次,不仅消耗CPU,而且生成新的查询计划也会占用缓存.
优点:每次执行计划都是最优的


【测试二】
现在换成带参数的形式.

--先清空计划缓存
DBCC FREEPROCCACHE

--	ProductID=870(4688行)	ProductID=897(2行)		
DECLARE @ProductID INT
SET @ProductID = 870
select sdh.SalesOrderID,sdh.SalesOrderNumber,P.ProductID,p.Name,sod.LineTotal
from [Sales].[SalesOrderHeader] sdh
inner join [Sales].[SalesOrderDetail] sod on sdh.SalesOrderID = sod.SalesOrderID
inner join [Production].[Product] p on sod.ProductID = p.ProductID
where P.ProductID =@ProductID

DECLARE @ProductID INT
SET @ProductID = 897
select sdh.SalesOrderID,sdh.SalesOrderNumber,P.ProductID,p.Name,sod.LineTotal
from [Sales].[SalesOrderHeader] sdh
inner join [Sales].[SalesOrderDetail] sod on sdh.SalesOrderID = sod.SalesOrderID
inner join [Production].[Product] p on sod.ProductID = p.ProductID
where P.ProductID =@ProductID

看计数器,同样有两个绿色的峰值为1.发生了2次编译 

--	再用视图查看缓存查询计划和计划大小
SELECT refcounts,usecounts,cacheobjtype,size_in_bytes,[text],query_plan
FROM sys.dm_exec_cached_plans 
CROSS APPLY sys.dm_exec_sql_text(plan_handle)
CROSS APPLY sys.dm_exec_query_plan(plan_handle)
WHERE [text] LIKE '%SalesOrderID%' AND [text] NOT LIKE '%sys%'



这会可以看到生成了两个相同的查询计划(query_plan),缓存大小(size_in_bytes)也就相同了.
还有另一点不同之处就是,执行计划分两部分执行,第一部分参数赋值,第二部分查询语句.
因此第二部分才用了相同的查询计划.

以上这种写法的优缺点是:
缺点: 如果查询条件值发生变化,每次都会作为新的查询语句编译第一次,不仅消耗CPU,而且生成新的查询计划也会占用缓存.
还有就是,由于查询计划相同.当返回行数相差较大.有的查询性能并不是较好的.
优点: 当返回数据量都差不多的时候是较好的,查询优化器根据参数估计一个较好的查询计划,有利于对查询计划进行控制.


【测试三】

--	这时把执行语句放到存储过程
CREATE PROCEDURE P_Test(@ProductID INT)
AS
BEGIN
select sdh.SalesOrderID,sdh.SalesOrderNumber,P.ProductID,p.Name,sod.LineTotal
from [Sales].[SalesOrderHeader] sdh
inner join [Sales].[SalesOrderDetail] sod on sdh.SalesOrderID = sod.SalesOrderID
inner join [Production].[Product] p on sod.ProductID = p.ProductID
where P.ProductID =@ProductID
END

--	ProductID=870(4688行)	ProductID=897(2行)
--	执行存储过程
DBCC FREEPROCCACHE
EXEC P_Test @ProductID = 870
EXEC P_Test @ProductID = 897


--	查看缓存对象执行类型:Proc(存储过程)
SELECT cacheobjtype,objtype,refcounts,usecounts,[sql]
FROM sys.syscacheobjects
WHERE [sql] LIKE '%SalesOrderID%' AND [sql] NOT LIKE '%sys%'

SELECT refcounts,usecounts,cacheobjtype,size_in_bytes,[text],query_plan
FROM sys.dm_exec_cached_plans 
CROSS APPLY sys.dm_exec_sql_text(plan_handle)
CROSS APPLY sys.dm_exec_query_plan(plan_handle)
WHERE [text] LIKE '%SalesOrderID%' AND [text] NOT LIKE '%sys%'


这时发现,只有1个缓存计划!无论参数怎么改变都是只缓存一个查询计划,这样就省去了内存的占用.
但是这个方法的优缺点就更明显了.


这种写法的优缺点是:
缺点: 如果查询条件值发生变化,每次都会编译1次,消耗CPU.
最重要的缺点是,查询计划的产生,是以第一次执行存储过程所传递的参数值来确定的!
也就是说,在存储过程创建后,传递参数首次执行存储过程,该参数返回的行数或多或少都会影响到执行计划的永久确定.

DBCC FREEPROCCACHE--情况计划缓存
EXEC P_Test @ProductID = 870--现在换870先执行
EXEC P_Test @ProductID = 897--刚才为897首次执行存储过程

执行后再看查询计划,又是不一样了!
所以这点要注意,为什么同样的存储过程,表统计信息没问题,但是有的查询快,有的慢.
跟踪把具体语句查出来运行又正常,就如同上面【测试一】一样。

这种情况的解决方法可以这样：EXEC P_Test @ProductID = 897 WITH RECOMPILE
使用WITH RECOMPILE时，系统只对当前存储过程编译并用该计划，并没有生成新的查询计划。系统缓存的还是原来的计划。


优点: 省下了内存! 不用每次编译



【测试四】

--	这时把执行语句放到存储过程
CREATE PROCEDURE P_Test2(@ProductID INT)
AS
BEGIN
DECLARE @ID INT
SET @ID = @ProductID --区别在这里
select sdh.SalesOrderID,sdh.SalesOrderNumber,P.ProductID,p.Name,sod.LineTotal
from [Sales].[SalesOrderHeader] sdh
inner join [Sales].[SalesOrderDetail] sod on sdh.SalesOrderID = sod.SalesOrderID
inner join [Production].[Product] p on sod.ProductID = p.ProductID
where P.ProductID =@ID 
END

--	ProductID=870(4688行)	ProductID=897(2行)
DBCC FREEPROCCACHE
EXEC P_Test2 @ProductID = 870
EXEC P_Test2 @ProductID = 897

SELECT refcounts,usecounts,cacheobjtype,size_in_bytes,[text],query_plan
FROM sys.dm_exec_cached_plans 
CROSS APPLY sys.dm_exec_sql_text(plan_handle)
CROSS APPLY sys.dm_exec_query_plan(plan_handle)
WHERE [text] LIKE '%SalesOrderID%' AND [text] NOT LIKE '%sys%'





这种方法优缺点与【测试三】其他一样，唯一不同的是，首次生成的执行计划不受参数影响。
如下两个存储过程，刚创建完存储过程后，不管谁先执行，查询计划都是一样的！
EXEC P_Test2 @ProductID = 870
EXEC P_Test2 @ProductID = 897

这里就真正用到了所谓的“参数嗅探”！因为优化引擎首次确定查询计划时，并不知道执行的参数值是什么。
因此只嗅探到传递的参数，系统就是根据参数确定了存储过程的查询计划。
这里也有不好的一点，就是参数返回多少也可能影响到性能。

对于这种存储过程，即使使用【WITH RECOMPILE】，计划还是一样。如下脚本，这种写法多余。

EXEC P_Test2 @ProductID = 870 WITH RECOMPILE
EXEC P_Test2 @ProductID = 897 WITH RECOMPILE

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

总结: 

以上几种都有优缺点，最不好的就是【测试二】那种。
还有一个现象，就是上面的所有测试，个人在性能监视器中都没有发现“重编译”的情况，每次都只有“编译”。
虽然编译包括重编译，但是重编译都没出现过一次。除非显示让语句重编译（如 option(recompile)）才出现。

测试一：最佳，每次都会生成新的计划缓存。但每次都编译并缓存

测试二：不好，同样缓存计划，返回结果集较大时性能不一样。每次都编译并缓存

测试三：省缓存，重用计划。随着表数据量增长，存储过程最好重新编译

测试四：省缓存，查询计划固定，更改不了。


最后总体测试对表以上这四种情况：
数据较多，不截图了，总结如下：


ProductID查询类型格式总逻辑读CPU内存时间每次编译缓存大小查询开销
870(4688行)即时查询where P.ProductID =87013052095220是56 KB26%
870(4688行)即时参数查询where P.ProductID =@ProductID130516101616是56 KB24%
870(4688行)存储过程proc ：@ProductID = 87013051792817是56 KB26%
870(4688行)存储过程内声明proc ：where P.ProductID =@ID13051898418是56 KB24%
897(2行)即时查询where P.ProductID =897201079210是48 KB13%
897(2行)即时参数查询where P.ProductID =@ProductID130517101617是56 KB37%
897(2行)存储过程proc ：P_Test @ProductID = 8972087608是56 KB13%
897(2行)存储过程内声明proc ：where P.ProductID =@ID13051898418是56 KB37%









薛定谔的DBA
关注
专栏目录
何谓SQLSERVER参数嗅探
霍力强的专栏
10-11 1912
http://www.cnblogs.com/lyhabc/archive/2013/03/02/2941144.html大家听到“嗅探”这个词应该会觉得跟黑客肯定有关系吧，使用工具嗅探一下参数，然后截获，脱裤o(∩_∩)o 。事实上，我觉得大家太敏感了，其实这篇文章跟数据库安全没有什么关系，实际上跟数据库性能调优有关相信大家有泡SQLSERVER论坛的话不多不少应该都会见过“参数嗅探”这几个字这
参数嗅探_SQL Server中SQL查询优化技术：参数嗅探
culuo4781的博客
07-21 1718
参数嗅探 描述 (Description) Of the many ways in which query performance can go awry, few are as misunderstood as parameter sniffing. Search the internet for solutions to a plan reuse problem, and many ...
SQL Server - 最佳实践 - 参数嗅探问题
weixin_34405354的博客
10-18 446
title: SQL Server - 最佳实践 - 参数嗅探问题 author: 风移 摘要 MSSQL Server参数嗅探既是一个涉及知识面非常广泛，又是一个比较难于解决的课题，即使对于数据库老手也是一个比较头痛的问题。这篇文章从参数嗅探是什么，如何产生，表象是什么，会带来哪些问题，如何解决这五个方面来探讨参数嗅探的来龙去脉，期望能够将SQL S...
sql server的参数嗅探
DRD
08-01 711
参数嗅探定义：当SQL Server第一次执行查询语句、或存储过程、或重新编译时，SQL Server会根据传入参数评估，来生成执行计划缓存，然后参数的值会 一并保存在执行计划缓存里。评估过程 即 参数嗅探。 具体sql: drop table op_param_sniffing -- 表定义，及数据插入 create table op_param_sniffing ( id bigint IDENTITY(1,1) primary key not null, product_id varchar(10
sql server参数嗅探（parameter sniffing）
最新发布
zhengyuepo的专栏
11-20 884
sql server参数嗅探（parameter sniffing）
SQL Server中参数化SQL写法遇到parameter sniff ，导致不合理执行计划重用的快速解决方法
12-15
参数嗅探是指SQL Server在编译存储过程或动态SQL时，会根据传递的第一个参数值来优化执行计划。当后续调用使用不同参数时，虽然参数值不同，但SQL Server仍会重用先前优化过的执行计划，这可能导致性能下降。 参数...
SQLSERVER参数嗅探详解与案例分析
参数嗅探可能导致SQLSERVER无法选择最佳执行计划，从而影响查询效率。文章提供了一些测试数据库和存储过程的示例，帮助读者更好地理解这一概念。" SQLSERVER参数嗅探是一个数据库性能优化领域的重要话题。在SQL...
SQL参数嗅探问题
醉酒的李白、的博客
09-01 613
SQL参数嗅探问题 最近遇到一个参数嗅探问题，何为参数嗅探？请自行查询了 解决办法： 使用动态拼接SQL绕过参数嗅探问题 declare @sql varchar(max) set @sql='SELECT * from...' exec(@sql) 至此查询速度就很快了，有多快呢？举个例子，嗅探影响的性能得17-20S甚至更长，绕过此问题就好比不加参数那样快，我这数据量1w就不足1S ...
SQL Server - 最佳实践 - 参数嗅探问题 转
zhou279818998的专栏
10-27 635
SQL Server - 最佳实践 - 参数嗅探问题 转
参数嗅探_SQL Server 2016参数嗅探
culuo4781的博客
07-27 582
参数嗅探 SQL Server tries always to generate the most optimized execution plan for each stored procedure the first time that the stored procedure is executed. The SQL Server Engine looks at the stored ...
T-SQL之变量导致索引无效/何谓SQLSERVER参数嗅探
starseeker7的专栏
03-12 2962
关于变量导致索引无效的情况，在使用工作中其实经常遇到，我看到一篇写的不错的文章就转载过来丰富我的收藏噶。 转载自：http://www.cnblogs.com/slade/archive/2012/04/29.html T-SQL之变量导致索引无效  (一)问题提出 1，在开发中是否遇到一个情况，就是在where后写明具体值时可以用到索引，使用变量时却不行了呢？ 2，是否
参数嗅探（Parameter Sniffing）（1/2)
weixin_34249367的博客
06-04 283
这个问题会在参数话的SQL语句（例如存储过程）与SQL Server里的计划缓存机制结合的时候会出现。这个文章分为2个部分，第1部分会介绍下参数嗅探（Parameter Sniffing）的概况，第2部分我们介绍下如何解决这个问题。 什么是参数嗅探（Parameter Sniffing） 在SQL Server里当你执行参数话的SQL查询时，查询优化器会基于第一个提供的参数值编译执行计划。然后...
网络嗅探器如何嗅探_SQL Server中的运行时常量嗅探
culuo4781的博客
07-28 348
网络嗅探器如何嗅探 Most of the people know about the so-called “Parameter Sniffing”. This topic was discussed in many aspects in a number of great articles. It is interesting that not only parameters might ...
模糊查询的参数嗅探
我想我是海 冬天的大海 心情随风轻摆
09-20 532
表 t , 有复合索引 ix_t_p1_p2，p1 的筛选性不是很高。 无论加不加 option(recompile) , 执行计划走的都是索引查找，但不加时， 因为无法准确识别参数中的%， 导致执行计划错误。 这个不用 option(recompile) 就无解了。因为毕竟是传入值，不能直接拼SQL（担心有注入风险）。 试过末尾添加： OPTION (OPTIMIZE F
参数嗅探（Parameter Sniffing）（2/2)
weixin_34008784的博客
06-05 186
在参数嗅探（Parameter Sniffing）（1/2)里，我介绍了SQL Server里参数嗅探的基本概念和背后的问题。如你所见，当缓存的计划被SQL Server盲目重用时，会带来严重的性能问题。今天我会向你展示下如何处理这个问题，即使用不同的技术克服它。 索引（Index） 上次我们讨论造成参数嗅探问题的根源是：在执行计划里，SQL 语句有时会产生书签查找，有时会产生表/聚集索引扫描...
解决SQL Server参数化SQL的parameter sniff问题及优化执行计划
参数嗅探是指SQL Server在编译存储过程或动态SQL时，会基于编译时的参数值来优化执行计划。如果这些参数值与实际运行时的参数值差异较大，尤其是当数据分布不均匀时，就会出现执行计划与实际需求不匹配的情况，从而...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值