SQL Server Parameter Sniffing及其改进方法-阿里云开发者社区

开发者社区> 阿里云支持与服务> 正文

SQL Server Parameter Sniffing及其改进方法

简介: 上一篇我们谈到,SQL Server 在处理存储过程的时候,为了节省编译时间,是一次编译,多次重用。当第一次运行时代入值产生的执行计划,不适用后续代入的参数时,就产生了parameter sniffing问题。
上一篇我们谈到,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

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

 2

exec Nosniff1 50000;

3

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;

5

6

exec Nosniff2 75124;

 7

8

如上一篇文章所述,使用本地变量,参数值在存储过程语句执行过程中得到,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;

9

语句级别:

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;

10

 

这两种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时,重用了执行计划,但是执行效率也不差。

11
  • 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去进行预估,生成执行计划。



版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

分享:

分享阿里云支持与服务团队最佳实践、经典案例与故障排查。

官方博客
文档