首页 > 代码库 > 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 =870 | 1305 | 20 | 952 | 20 | 是 | 56 KB | 26% |
870(4688行) | 即时参数查询 | where P.ProductID =@ProductID | 1305 | 16 | 1016 | 16 | 是 | 56 KB | 24% |
870(4688行) | 存储过程 | proc :@ProductID = 870 | 1305 | 17 | 928 | 17 | 是 | 56 KB | 26% |
870(4688行) | 存储过程内声明 | proc :where P.ProductID =@ID | 1305 | 18 | 984 | 18 | 是 | 56 KB | 24% |
897(2行) | 即时查询 | where P.ProductID =897 | 20 | 10 | 792 | 10 | 是 | 48 KB | 13% |
897(2行) | 即时参数查询 | where P.ProductID =@ProductID | 1305 | 17 | 1016 | 17 | 是 | 56 KB | 37% |
897(2行) | 存储过程 | proc :P_Test @ProductID = 897 | 20 | 8 | 760 | 8 | 是 | 56 KB | 13% |
897(2行) | 存储过程内声明 | proc :where P.ProductID =@ID | 1305 | 18 | 984 | 18 | 是 | 56 KB | 37% |
SQL Server 查询优化(测试02)参数嗅探-执行计划选择