SQL Server Parameter Sniffing及其改进方法

sql server 在处理存储过程的时候，为了节省编译时间，是一次编译，多次重用。当第一次运行时代入值产生的执行计划，不适用后续代入的参数时，就产生了parameter sniffing问题。 create procedure sniff1(@i int) as select count(b.salesorderid),sum(p.weight) from [sale
sql server 在处理存储过程的时候，为了节省编译时间，是一次编译，多次重用。当第一次运行时代入值产生的执行计划，不适用后续代入的参数时，就产生了parameter sniffing问题。

create procedure sniff1(@i int) as 
select count(b.salesorderid),sum(p.weight) from 
[sales].[salesorderheader] a
inner join [sales].[salesorderdetail] b
on a.salesorderid = b.salesorderid
inner join production.product p
on b.productid = p.productid
where a.salesorderid =@i;
go
dbcc freeproccache
exec sniff1 50000;
exec sniff1 75124;
go

parameter sniffing问题发生不频繁，只会发生在数据分布不均匀或者代入参数值不均匀的情况下。现在，我们就来探讨下如何解决这类问题。

1. 使用exec() 方式运行动态sql

create procedure nosniff1(@i int) as 
declare @cmd varchar(1000);
set @cmd = 'select count(b.salesorderid),sum(p.weight) from 
[sales].[salesorderheader] a
inner join [sales].[salesorderdetail] b
on a.salesorderid = b.salesorderid
inner join production.product p
on b.productid = p.productid
where a.salesorderid =';
 exec(@cmd+@i); 
go

exec nosniff1 50000;

exec nosniff1 75124;

从上述trace中可以看到，在执行查询语句之前，都有sp: cacheinsert事件，sql server做了动态编译，根据变量的值，都正确的预估了结果集，给出了不同的执行计划。

2. 使用本地变量

create procedure nosniff2(@i int) as 
declare @iin int;
set @iin=@i
select count(b.salesorderid),sum(p.weight) from 
[sales].[salesorderheader] a
inner join [sales].[salesorderdetail] b
on a.salesorderid = b.salesorderid
inner join production.product p
on b.productid = p.productid
where a.salesorderid =@iin;
go

exec nosniff2 50000;

exec nosniff2 75124;

如上一篇文章所述，使用本地变量，参数值在存储过程语句执行过程中得到，sql server在运行时不知道变量的值，会根据一个预估值进行编译，给出一个折中的执行计划。

3. 使用query hint，指定执行计划

在 select、delete、update 和 merge 语句最后加上option ( [ ,...n ] )，对执行计划进行指导。当数据库管理员知道问题所在时，可以通过hint引导sql server生成一个对所有变量都不太差的执行计划。

以上所述是小编给大家介绍的sql server parameter sniffing及其改进方法，希望对大家有所帮助