SQL Server Parameter Sniffing及其改進方法

2024-08-31 01:04:58

字體：大中小

來源：轉載

供稿：網友

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] ainner join [Sales].[SalesOrderDetail] bon a.SalesOrderID = b.SalesOrderIDinner join Production.Product pon b.ProductID = p.ProductIDwhere a.SalesOrderID =@i;goDBCC FREEPROCCACHEexec Sniff1 50000;exec Sniff1 75124;go

SQLServer,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] ainner join [Sales].[SalesOrderDetail] bon a.SalesOrderID = b.SalesOrderIDinner join Production.Product pon b.ProductID = p.ProductIDwhere a.SalesOrderID ='; exec(@cmd+@i); go

SQLServer,Parameter,Sniffing

exec Nosniff1 50000;

SQLServer,Parameter,Sniffing

exec Nosniff1 75124;

從上述trace中可以看到，在執行查詢語句之前，都有SP: CacheInsert事件，SQL Server做了動態編譯，根據變量的值，都正確的預估了結果集，給出了不同的執行計劃。

2. 使用本地變量

create procedure Nosniff2(@i int) as declare @iin int;set @iin=@iSELECT count(b.SalesOrderID),sum(p.weight) from [Sales].[SalesOrderHeader] ainner join [Sales].[SalesOrderDetail] bon a.SalesOrderID = b.SalesOrderIDinner join Production.Product pon b.ProductID = p.ProductIDwhere a.SalesOrderID =@iin;go

exec Nosniff2 50000;

SQLServer,Parameter,Sniffing