首页 > 代码库 > SQL Server 查询优化(测试02)参数嗅探-执行计划选择

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

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

去官网下载了数据库: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首次执行存储过程

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




优点: 省下了内存!(不过内存一般用不了多少)



【测试四】

--	这时把执行语句放到存储过程
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


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


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

总结: 

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

测试一:最佳,每次都会生成新的计划缓存

测试二:不好,同样缓存计划,返回结果集较大时性能不一样

测试三:省缓存,随着数据量增长,存储过程最好重新编译

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


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


ProductID查询类型格式总逻辑读CPU内存时间每次编译缓存大小查询开销
870(4688行)即时查询where P.ProductID =8701305209522056 KB26%
870(4688行)即时参数查询where P.ProductID =@ProductID13051610161656 KB24%
870(4688行)存储过程proc :@ProductID = 8701305179281756 KB26%
870(4688行)存储过程内声明proc :where P.ProductID =@ID1305189841856 KB24%
897(2行)即时查询where P.ProductID =89720107921048 KB13%
897(2行)即时参数查询where P.ProductID =@ProductID13051710161756 KB37%
897(2行)存储过程proc :P_Test @ProductID = 897208760856 KB13%
897(2行)存储过程内声明proc :where P.ProductID =@ID1305189841856 KB37%









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