SQL Server Parameter Sniffing及其改進(jìn)方法

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

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問(wèn)題發(fā)生不頻繁，只會(huì)發(fā)生在數(shù)據(jù)分布不均勻或者代入?yún)?shù)值不均勻的情況下?，F(xiàn)在，我們就來(lái)探討下如何解決這類問(wèn)題。

1. 使用Exec() 方式運(yùn)行動(dòng)態(tài)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中可以看到，在執(zhí)行查詢語(yǔ)句之前，都有SP: CacheInsert事件，SQL Server做了動(dòng)態(tài)編譯，根據(jù)變量的值，都正確的預(yù)估了結(jié)果集，給出了不同的執(zhí)行計(jì)劃。

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;

如上一篇文章所述，使用本地變量，參數(shù)值在存儲(chǔ)過(guò)程語(yǔ)句執(zhí)行過(guò)程中得到，SQL Server在運(yùn)行時(shí)不知道變量的值，會(huì)根據(jù)一個(gè)預(yù)估值進(jìn)行編譯，給出一個(gè)折中的執(zhí)行計(jì)劃。

3. 使用Query Hint，指定執(zhí)行計(jì)劃

在 SELECT、DELETE、UPDATE 和 MERGE 語(yǔ)句最后加上OPTION ( [ ,...n ] )，對(duì)執(zhí)行計(jì)劃進(jìn)行指導(dǎo)。當(dāng)數(shù)據(jù)庫(kù)管理員知道問(wèn)題所在時(shí)，可以通過(guò)hint引導(dǎo)SQL Server生成一個(gè)對(duì)所有變量都不太差的執(zhí)行計(jì)劃。

以上所述是小編給大家介紹的SQL Server Parameter Sniffing及其改進(jìn)方法，希望對(duì)大家有所幫助，如果大家有任何疑問(wèn)請(qǐng)給我留言，小編會(huì)及時(shí)回復(fù)大家的。在此也非常感謝大家對(duì)腳本之家網(wǎng)站的支持！