SQL Server Parameter Sniffing及改进示例

这篇文章主要为大家详细介绍了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及其改进方法，希望对大家有所帮助，如果大家有任何疑问请给我留言，小编会及时回复大家的。在此也非常感谢大家对四海网网站的支持！

本文来自：http://www.q1010.com/179/9149-0.html

注：关于SQL Server Parameter Sniffing及改进示例的内容就先介绍到这里，更多相关文章的可以留意四海网的其他信息。

关键词：SQL SERVER