SQLSERVER参数嗅探问题的示例分析

共计 4377 个字符，预计需要花费 11 分钟才能阅读完成。

丸趣 TV 小编给大家分享一下 SQLSERVER 参数嗅探问题的示例分析，相信大部分人都还不怎么了解，因此分享这篇文章给大家参考一下，希望大家阅读完这篇文章后大有收获，下面让我们一起去了解一下吧！

下面测试数据库的备份文件，里面有一些表和一些测试数据，因为我下面用的测试表都是这个数据库里的

只需要还原数据库就可以了，这个数据库是 SQL2005 版本的，数据库名：AdventureWorks

下面只需要用到三张表，表里面有索引：

[Production].[Product] [SalesOrderHeader_test] [SalesOrderDetail_test]

数据库下载链接：AdventureWorks

其实简单来讲，参数嗅探我的很通俗的解释就是：SQLSERVER 用鼻子嗅不到具体参数是多少

所以他不能选择最合适的执行计划去执行你的查询，所以参数嗅探是一个不好的现象。

想真正了解参数嗅探，大家可以先创建下面两个存储过程

存储过程一：

USE [AdventureWorks]
DROP PROC Sniff
CREATE PROC Sniff(@i INT)
SELECT COUNT(b.[SalesOrderID]),SUM(p.[Weight])
FROM [dbo].[SalesOrderHeader_test] a
INNER JOIN [dbo].[SalesOrderDetail_test] b
ON a.[SalesOrderID]=b.[SalesOrderID]
INNER JOIN [Production].[Product] p
ON b.[ProductID]=p.[ProductID]
WHERE a.[SalesOrderID]=@i
GO

存储过程二：

 复制代码   代码如下:1 USE [AdventureWorks] 2 GO 3 DROP PROC Sniff2 4 GO 5 CREATE PROC Sniff2(@i INT) 6 AS 7 DECLARE @j INT 8 SET @j=@i 9 SELECT COUNT(b.[SalesOrderID]),SUM(p.[Weight])10 FROM [dbo].[SalesOrderHeader_test] a11 INNER JOIN [dbo].[SalesOrderDetail_test] b12 ON a.[SalesOrderID]=b.[SalesOrderID]13 INNER JOIN [Production].[Product] p14 ON b.[ProductID]=p.[ProductID]15 WHERE a.[SalesOrderID]=@j16 GO

然后请做下面这两个测试

测试一：

-- 测试一：USE [AdventureWorks]
DBCC freeproccache
EXEC [dbo].[Sniff] @i = 500000 -- int
-- 发生编译，插入一个使用 nested loops 联接的执行计划
EXEC [dbo].[Sniff] @i = 75124 -- int
-- 发生执行计划重用，重用上面的 nested loops 的执行计划
GO

测试二：

-- 测试二：USE [AdventureWorks]
DBCC freeproccache
SET STATISTICS PROFILE ON
EXEC [dbo].[Sniff] @i = 75124 -- int
-- 发生编译，插入一个使用 hash match 联接的执行计划
EXEC [dbo].[Sniff] @i = 50000 -- int
-- 发生执行计划重用，重用上面的 hash match 的执行计划
GO

从上面两个测试可以清楚地看到执行计划重用的副作用。

由于数据分布差别很大参数 50000 和 75124 只对自己生成的执行计划有好的性能，

如果使用对方生成的执行计划，性能就会下降。参数 50000 返回的结果集比较小，

所以性能下降不太严重。参数 75124 返回的结果集大，就有了明显的性能下降，两个执行计划的差别有近 10 倍

对于这种因为重用他人生成的执行计划而导致的水土不服现象，SQSERVERL 有一个专有名词，叫“参数嗅探 parameter sniffing”

因为语句的执行计划对变量的值很敏感，而导致重用执行计划会遇到性能问题，就是我上面说的

“

SQLSERVER 用鼻子嗅不到具体参数是多少，所以他不能选择最合适的执行计划去执行你的查询

”

本地变量的影响

那对于有 parameter sniffing 问题的存储过程，如果使用本地变量，会怎样呢？

下面请看测试 3。这次用不同的变量值时，都清空执行计划缓存，迫使其重编译

-- 第一次
USE [AdventureWorks]
DBCC freeproccache
SET STATISTICS TIME ON
SET STATISTICS PROFILE ON
EXEC [dbo].[Sniff] @i = 50000 -- int
GO

-- 第二次
USE [AdventureWorks]
DBCC freeproccache
SET STATISTICS TIME ON
SET STATISTICS PROFILE ON
EXEC [dbo].[Sniff] @i = 75124 -- int
GO

-- 第三次
USE [AdventureWorks]
DBCC freeproccache
SET STATISTICS TIME ON
SET STATISTICS PROFILE ON
EXEC [dbo].[Sniff2] @i = 50000 -- int
GO

-- 第四次
USE [AdventureWorks]
DBCC freeproccache
SET STATISTICS TIME ON
SET STATISTICS PROFILE ON
EXEC [dbo].[Sniff2] @i = 75124 -- int
GO

看他们的执行计划：

对于第一句和第二句，因为 SQL 在编译的时候知道变量的值，所以在做 EstimateRows 的时候，做得非常准确，选择了最适合他们的执行计划

但是对于第三句和第四句，SQLSERVER 不知道 @j 的值是多少，所以在做 EstimateRows 的时候，不管代入的 @i 值是多少，

一律给 @j 一样的预测结果。所以两个执行计划是完全一样的（都是 Hash Match）。

参数嗅探的解决办法

参数嗅探的问题发生的频率并不高，他只会发生在一些表格里的数据分布很不均匀，或者用户带入的参数值很不均匀的情况下。

由于篇幅原因我就不具体说了, 只是做一些归纳

（1）用 exec() 的方式运行动态 SQL

如果在存储过程里不是直接运行语句，而是把语句带上变量，生成一个字符串，再让 exec() 这样的命令做动态语句运行，

那 SQL 就会在运行到这句话的时候，对动态语句进行编译。

这时 SQL 已经知道了变量的值，会根据生成优化的执行计划，从而绕过参数嗅探问题

-- 例如前面的存储过程 Sniff，就可以改成这样
USE [AdventureWorks]
DROP PROC NOSniff
CREATE PROC NOSniff(@i INT)
DECLARE @cmd VARCHAR(1000)
SET @cmd= SELECT COUNT(b.[SalesOrderID]),SUM(p.[Weight])
FROM [dbo].[SalesOrderHeader_test] a
INNER JOIN [dbo].[SalesOrderDetail_test] b
ON a.[SalesOrderID]=b.[SalesOrderID]
INNER JOIN [Production].[Product] p
ON b.[ProductID]=p.[ProductID]
WHERE a.[SalesOrderID]= 
EXEC(@cmd+@i)
GO

（2）使用本地变量 local variable

（3）在语句里使用 query hint，指定执行计划

在 select,insert,update,delete 语句的最后，可以加一个 option(query_hint) 的子句

对 SQLSERVER 将要生成的执行计划进行指导。当 DBA 知道问题所在以后，可以通过加 hint 的方式，引导

SQL 生成一个比较安全的，对所有可能的变量值都不差的执行计划

USE [AdventureWorks]
DROP PROC NoSniff_QueryHint_Recompile
CREATE PROC NoSniff_QueryHint_Recompile(@i INT) 
SELECT COUNT(b.[SalesOrderID]),SUM(p.[Weight])
FROM [dbo].[SalesOrderHeader_test] a
INNER JOIN [dbo].[SalesOrderDetail_test] b
ON a.[SalesOrderID]=b.[SalesOrderID]
INNER JOIN [Production].[Product] p
ON b.[ProductID]=p.[ProductID]
WHERE a.[SalesOrderID]=@i
OPTION(RECOMPILE)
GO

（4）Plan Guide

可以用下面的方法，在原来那个有参数嗅探问题的存储过程“Sniff”上，解决 sniffing 问题

USE [AdventureWorks]
EXEC [sys].[sp_create_plan_guide]
@name=N Guide1 ,
@stmt=N SELECT COUNT(b.[SalesOrderID]),SUM(p.[Weight])
FROM [dbo].[SalesOrderHeader_test] a
INNER JOIN [dbo].[SalesOrderDetail_test] 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 Sniff ,
@params=NULL,
@hints=N option(optimize for(@i=75124)) 
GO

以上是“SQLSERVER 参数嗅探问题的示例分析”这篇文章的所有内容，感谢各位的阅读！相信大家都有了一定的了解，希望分享的内容对大家有所帮助，如果还想学习更多知识，欢迎关注丸趣 TV 行业资讯频道！