查看: 1680|回复: 0

[SQLServer] SQL Server Parameter Sniffing及其改进方法

发表于 2017-10-8 08:00:01
句号论坛

SQL Server 在处理存储过程的时候,为了节省编译时间,是一次编译,多次重用。当第一次运行时代入值产生的执行计划,不适用后续代入的参数时,就产生了parameter sniffing问题。 create procedure Sniff1(@i int) as SELECT count(b.SalesOrderID),sum(p.weight) from [Sale
SQL Server 在处理存储过程的时候,为了节省编译时间,是一次编译,多次重用。当第一次运行时代入值产生的执行计划,不适用后续代入的参数时,就产生了parameter sniffing问题。

  1. create procedure Sniff1(@i int) as
  2. SELECT count(b.SalesOrderID),sum(p.weight) from
  3. [Sales].[SalesOrderHeader] a
  4. inner join [Sales].[SalesOrderDetail] b
  5. on a.SalesOrderID = b.SalesOrderID
  6. inner join Production.Product p
  7. on b.ProductID = p.ProductID
  8. where a.SalesOrderID =@i;
  9. go
  10. DBCC FREEPROCCACHE
  11. exec Sniff1 50000;
  12. exec Sniff1 75124;
  13. go
复制代码

Parameter Sniffing问题发生不频繁,只会发生在数据分布不均匀或者代入参数值不均匀的情况下。现在,我们就来探讨下如何解决这类问题。

1. 使用Exec() 方式运行动态SQL

  1. create procedure Nosniff1(@i int) as
  2. declare @cmd varchar(1000);
  3. set @cmd = 'SELECT count(b.SalesOrderID),sum(p.weight) from
  4. [Sales].[SalesOrderHeader] a
  5. inner join [Sales].[SalesOrderDetail] b
  6. on a.SalesOrderID = b.SalesOrderID
  7. inner join Production.Product p
  8. on b.ProductID = p.ProductID
  9. where a.SalesOrderID =';
  10. exec(@cmd+@i);
  11. go
复制代码

exec Nosniff1 50000;

exec Nosniff1 75124;

从上述trace中可以看到,在执行查询语句之前,都有SP: CacheInsert事件,SQL Server做了动态编译,根据变量的值,都正确的预估了结果集,给出了不同的执行计划。

2. 使用本地变量

  1. create procedure Nosniff2(@i int) as
  2. declare @iin int;
  3. set @iin=@i
  4. SELECT count(b.SalesOrderID),sum(p.weight) from
  5. [Sales].[SalesOrderHeader] a
  6. inner join [Sales].[SalesOrderDetail] b
  7. on a.SalesOrderID = b.SalesOrderID
  8. inner join Production.Product p
  9. on b.ProductID = p.ProductID
  10. where a.SalesOrderID =@iin;
  11. 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及其改进方法,希望对大家有所帮助,如果大家有任何疑问请给我留言,小编会及时回复大家的。在此也非常感谢大家对程序员之家网站的支持!



回复

使用道具 举报

关闭

站长推荐上一条 /1 下一条