SQL Server Parameter Sniffing及其改进方法_Sql Server

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

SQL Server Parameter Sniffing及其改进方法

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

SQL Server Parameter Sniffing及其改进方法

exec Nosniff1 50000;

SQL Server Parameter Sniffing及其改进方法

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