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

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 ( <query_hint> [ ,...n ] )，對執行計劃進行指導。當數據庫管理員知道問題所在時，可以通過hint引導SQL Server生成一個對所有變量都不太差的執行計劃。

 

SQL Server 的Query Hint有十幾種，具體信息參考https://docs.microsoft.com/en-us/sql/t-sql/queries/hints-transact-sql-query 。

針對parameter sniffing的問題，有幾種hint可以使用。

• Recompile

Recompile有兩種方式，一種是存儲過程級別的，一種是語句級別的。具體實現方式如下。

存儲過程級別：

create procedure Nosniff_Recompile(@i int)
with recompile 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
exec Nosniff_Recompile 75124;

語句級別：

create procedure Nosniff_Recompile2(@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 option(recompile)
go
exec Nosniff_Recompile2 75124;

 

這兩種recompile方式都可以獲得準確的執行計劃，區別在於，如果是語句級別的recompile，那麼存儲過程級別的執行計劃重用還是會有的，隻有運行到指定語句的時候才會重新編譯這個語句。如果存儲過程很複雜，存在多個語句，可以指定某個語句中進行重編譯，這是一種精細化的調優方式。

• Optimize for ( @variable_name { UNKNOWN | = literal_constant } [ , ...n ] )

當確認parameter sniffing後，確定了某個參數值重用執行計劃會特別慢，那麼可以使用optimize for 某個參數，來傾向性的生成執行計劃。

create procedure Nosniff_OptimizeFor(@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 option(optimize for(@i=75124))
go
exec Nosniff_OptimizeFor 50000;
exec Nosniff_OptimizeFor 75124;

執行50000時，會生成一個執行計劃，不過執行計劃是根據75124來預估的，所以執行75124時，重用了執行計劃，但是執行效率也不差。

• Plan guid

Exec sp_create_plan_guide
@name='Guide1',
@stmt='
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',
@type=N'object',
@module_or_batch=N'Sniff1',
@params=NULL,
@hints=N'option(optimize for(@i=75124))';
go

當下次調用存儲過程Sniff1時，不管輸入的參數是什麼，都會根據75124去進行預估，生成執行計劃。