SQL Server 在處理存儲(chǔ)過程的時(shí)候，為了節(jié)省編譯時(shí)間，是一次編譯，多次重用。當(dāng)?shù)谝淮芜\(yùn)行時(shí)代入值產(chǎn)生的執(zhí)行計(jì)劃，不適用后續(xù)代入的參數(shù)時(shí)，就產(chǎn)生了parameter sniffing問題。 create procedure Sniff1(@i int) as SELECT count(b.SalesOrderID),sum(p.weight) from [Sale
SQL Server 在處理存儲(chǔ)過程的時(shí)候，為了節(jié)省編譯時(shí)間，是一次編譯，多次重用。當(dāng)?shù)谝淮芜\(yùn)行時(shí)代入值產(chǎn)生的執(zhí)行計(jì)劃，不適用后續(xù)代入的參數(shù)時(shí)，就產(chǎn)生了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