27,581
社区成员




--13.4 Parameter Sniffing
--在使用存储过程的时候,总是要使用到一些变量。变量有两种,一种是在存储过程的外面定义的。
--在调用存储过程的时候,必须要给它带入值。这种变量,SQL Server在编译的时候知道它的值是多少。
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
--这里的变量@i,就是要在调用的时候带入值的。
exec Sniff 50000
go
--还有一种变量是在存储过程里面定义的。它的值是在存储过程的语句执行的过程中得到的。所以对这种本地变量,SQL Server在编译的时候不知道它的值是多少。例如:
create proc Sniff2 (@i int)
as
declare @j int --这里的变量@j,是SQL在运行的过程中算出来的。
set @j = @i
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 =@j
go
--13.4.1 什么是”Parameter Sniffing”
--带着第一个问题,请做下面这两个测试。
--测试 1:第二个效率很差
dbcc freeproccache
go
exec Sniff 50000 -- 发生编译,插入一个使用Nested Loops联结的执行计划
go
exec Sniff 75124 -- 发生执行计划重用,重用上面的Nested Loops的执行计划
go
--测试2:执行计划完全一样,但是2个效率都很差
dbcc freeproccache
go
exec Sniff 75124 -- 发生编译,插入一个使用Hash Match联结的执行计划
go
exec Sniff 50000 -- 发生执行计划重用,重用上面的Hash Match的执行计划
go
--13.4.2 本地变量的影响
--那对于有Parameter Sniffing问题的存储过程,如果使用本地变量,会怎么样呢?
--下面请看测试3。这次用不同的变量值时,都清空执行计划缓存,迫使其重编译。
dbcc freeproccache
go
exec Sniff 50000
go
dbcc freeproccache
go
exec Sniff 75124 --删除了缓存的执行计划,所以不会重用执行计划,会重新编译产生更优的执行计划.
go
dbcc freeproccache
go
exec Sniff2 50000
go
/*========================================================================
由于删除了缓存的执行计划,所以需要重新编译,会根据某些规则来猜测一个值,这样产生的执行计划可能不是合适.
另外,此执行计划于上面那句是一样的,之所以这样,是因为SQL Server在编译时没法知道这个本地变量的值,
所以只能根据表中数据的分布做一个猜测,而这个猜测的值是一样的,就会导致产生相同的执行计划.
==========================================================================*/
dbcc freeproccache
go
exec Sniff2 75124
go
[/quote]
有点意思。仔细看看。
其实我不是SQL科班出生,这东西真还没接触过,哈哈。--13.4.3 Parametre Sniffing的解决方案
--1. 用EXEC()的方式运行动态SQL语句
--如果在存储过程里不是直接运行语句,而是把语句带上变量,生成一个字符串,再让Exec()这样的命令做动态语句运行,那SQL Server就会在运行到这句话的时候,
--对动态语句进行编译,不会重用执行计划。这时SQL Server已经知道了变量的值,会根据值生成优化的执行计划,从而绕过了parameter sniffing的问题。
--例如前面那个Sniff存储过程,就可以改成下面的样子。
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
--会得到比它更好的性能。
Dbcc freeproccache
go
exec NoSniff 50000
go
exec NoSniff 75124
go
/*===================================================
2.使用本地变量 local variable
前面提到如果把变量赋给一个本地变量(就是在存储过程内部定义的变量),SQL Server在编译时是没办法知道这个本地变量的值的.
所以会根据表格里数据的一般分布情况,猜测一个返回值,不管用户在调用存储过程时代入的变量值是多少,产生的执行计划都是一样的.
而这样的执行计划一般比较中庸,不会是最优的执行计划,但是对大多数变量值来说,也不会是一个很差的执行计划.
上面13.4.2 本地变量的影响中的Sniff2就是这样的一个例子.
=====================================================*/
--3.在语句中使用query hint,指定执行计划
/*==========================================================
3.1 "Recompile”
"Recompile”这个查询提示告诉SQL Server,语句在每一次存储过程运行的时候,都要重新编译一下。
与上面的使用exec执行动态语句一样,每次运行都会重新编译一次,不会重用执行计划
这样就能够使SQL Server根据当前变量的值,选一个最好的执行计划。
对前面的那个例子,我们可以这么改写。
============================================================*/
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
Dbcc freeproccache
go
exec NoSniff_QueryHint_Recompile 50000
go
exec NoSniff_QueryHint_Recompile 75124
go
--3.2 和这种方法类似的,是在存储过程的定义里直接指定"recompile",也能达到避免parameter sniffing的效果。
drop proc NoSniff_SPCreate_Recompile
go
create proc NoSniff_SPCreate_Recompile (@i int)
with recompile
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
Dbcc freeproccache
go
exec NoSniff_SPCreate_Recompile 50000
go
exec NoSniff_SPCreate_Recompile 75124
go
/*==========================================================================
3.3 指定Join运算({ LOOP | MERGE | HASH } JOIN)
但这种方法一般很不推荐,因为不是所有的join,SQL Server都能根据你给的提示做出执行计划的.
如果对上面的存储过程的查询上加上: option(hash join),也就是对这个查询内所有的join都用hash算法,
那么运行时会报错:
Query processor could not produce a query plan because of the hints defined in this query.
Resubmit the query without specifying any hints and without using set forceplan.
也就是SQL Server不接受这个查询提示。
所以更常见的,是在特定的那个Join上使用Join Hint。这种方法成功的几率要高得多。
这种方法的好处是保持了存储过程一次编译,后面多次使用的特性,节省编译时间。
但缺点是这种方法生成的执行计划,不一定就是一个最好的执行计划,
而且表里的数据量发生变化后,现在合适的join方式将来可能不合适,到时候会造成性能问题。
===========================================================================*/
drop proc NoSniff_QueryHint_JoinHint
go
create proc NoSniff_QueryHint_JoinHint (@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 hash join Production.Product p
on b.ProductID = p.ProductID
where a.SalesOrderID = @i
go
Dbcc freeproccache
go
exec NoSniff_QueryHint_JoinHint 50000
go
exec NoSniff_QueryHint_JoinHint 75124
go
/*==========================================================================
3.4 OPTIMIZE FOR ( @variable_name = literal_constant [ , ...n ] )
当确认了Parameter Sniffing问题后,会发现,根据某些变量值生成的执行计划,快慢相差很大,
而根据另外一些变量生成的执行计划,性能在好和坏时,相差并不大。
使用” OPTIMIZE FOR”这个查询指导,就能够让SQL Server做到这一点,而且比上面。
比如:当变量值 = 5000,用最好的Nested Loops执行计划,用时10多毫秒,用hash join执行计划,也不过300毫秒
当变量值 = 75142,用hash join执行计划,需要500毫秒,而用Nested Loops时要用4000多毫秒。
所以折中一下,还是用hash join比较稳定。
使用OPTIMIZE FOR这个查询指导,就能够让SQL Server用变量值75142来生产hash join的执行计划,
这样的执行计划就一个比较稳定的查询计划。
它的局限性就是要去修改存储过程的定义,有些时候没有应用开发组的许可,是不可以修改的。
而对sp_executesql的方式调用指令,问题更大,因为指令是写在应用程序中的,而不是在SQL Server中,管理员没法去修改应用程序。
好在SQL Server 2005和2008,引入和完善了一个Plan Guide的功能,DBA可以告诉SQL Server,当运行某个语句时,请用我们指定的执行计划,
这样就不需要修改存储过程或应用了。
===========================================================================*/
drop proc NoSniff_QueryHint_OptimizeFor
go
create proc NoSniff_QueryHint_OptimizeFor (@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 (optimize for (@i = 75124))
go
Dbcc freeproccache
go
exec NoSniff_QueryHint_OptimizeFor 50000
go
exec NoSniff_QueryHint_OptimizeFor 75124
go
--3.5 Plan Guild
--例如可以用下面的方法,在原来那个有parameter sniffing问题的存储过程”Sniff”上,解决sniffing问题。
EXEC 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
--使用了75124的Hash Match的Join方式
exec Sniff 50000
go
/*==========================================================================
上面各种方法的比较:
方法 是否修改存储过程/语句 是否每次运行都要重编译 执行计划准确度
用exec()的方式运行动态sql 需要 会 很准确
使用本地变量(local variable) 需要 不会 一般
Query Hint + Reconmpile 需要 会 很准确
Query Hint + 指定Join运算 需要 不会 很一般
Query HInt + Optimize For 需要 不会 比较准确
Plan Guide 不需要 不会 比较准确
===========================================================================*/
--13.4 Parameter Sniffing
--在使用存储过程的时候,总是要使用到一些变量。变量有两种,一种是在存储过程的外面定义的。
--在调用存储过程的时候,必须要给它带入值。这种变量,SQL Server在编译的时候知道它的值是多少。
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
--这里的变量@i,就是要在调用的时候带入值的。
exec Sniff 50000
go
--还有一种变量是在存储过程里面定义的。它的值是在存储过程的语句执行的过程中得到的。所以对这种本地变量,SQL Server在编译的时候不知道它的值是多少。例如:
create proc Sniff2 (@i int)
as
declare @j int --这里的变量@j,是SQL在运行的过程中算出来的。
set @j = @i
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 =@j
go
--13.4.1 什么是”Parameter Sniffing”
--带着第一个问题,请做下面这两个测试。
--测试 1:第二个效率很差
dbcc freeproccache
go
exec Sniff 50000 -- 发生编译,插入一个使用Nested Loops联结的执行计划
go
exec Sniff 75124 -- 发生执行计划重用,重用上面的Nested Loops的执行计划
go
--测试2:执行计划完全一样,但是2个效率都很差
dbcc freeproccache
go
exec Sniff 75124 -- 发生编译,插入一个使用Hash Match联结的执行计划
go
exec Sniff 50000 -- 发生执行计划重用,重用上面的Hash Match的执行计划
go
--13.4.2 本地变量的影响
--那对于有Parameter Sniffing问题的存储过程,如果使用本地变量,会怎么样呢?
--下面请看测试3。这次用不同的变量值时,都清空执行计划缓存,迫使其重编译。
dbcc freeproccache
go
exec Sniff 50000
go
dbcc freeproccache
go
exec Sniff 75124 --删除了缓存的执行计划,所以不会重用执行计划,会重新编译产生更优的执行计划.
go
dbcc freeproccache
go
exec Sniff2 50000
go
/*========================================================================
由于删除了缓存的执行计划,所以需要重新编译,会根据某些规则来猜测一个值,这样产生的执行计划可能不是合适.
另外,此执行计划于上面那句是一样的,之所以这样,是因为SQL Server在编译时没法知道这个本地变量的值,
所以只能根据表中数据的分布做一个猜测,而这个猜测的值是一样的,就会导致产生相同的执行计划.
==========================================================================*/
dbcc freeproccache
go
exec Sniff2 75124
go