萬盛學電腦網

 萬盛學電腦網 >> 數據庫 >> mssql數據庫 >> SQL Server Parameter Sniffing及其改進方法

SQL Server Parameter Sniffing及其改進方法

SQL Server Parameter Sniffing及其改進方法

投稿:mrr 字體:[增加 減小] 類型:轉載 時間:2017-06-29 我要評論

這篇文章主要介紹了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及其改進方法,希望對大家有所幫助,如果大家有任何疑問請給我留言,小編會及時回復大家的。在此也非常感謝大家對 網站的支持!

copyright © 萬盛學電腦網 all rights reserved