SQL Server中参数化SQL写法遇到parameter sniff ，导致不合理执行计划重用的快速解决方法

摘要：parametersniff问题是重用其他参数生成的执行计划，导致当前参数采用该执行计划非最优化的现象。想必熟悉数据的同学都应该知道，产生p...

parameter sniff问题是重用其他参数生成的执行计划，导致当前参数采用该执行计划非最优化的现象。想必熟悉数据的同学都应该知道，产生parameter sniff最典型的问题就是使用了参数化的SQL（或者存储过程中使用了参数化）写法，如果存在数据分布不均匀的情况下，正常情况下生成的执行计划，在传入在分布数据较多的参数的情况下，重用了正常参数生成的执行计划，而这种缓存的执行计划并非适合当前参数的一种情况。

这种情况，在实际业务中，出现的频率还是比较高的，因为存储过程一般都是采用参数化的写法，这时，遇到分布不均匀的数据参数时，parameter sniff现象就出现了，这种问题还是比较让人头疼的。

具体parameter sniff产生的原因，我就不做过多的解释了，解释这个就显得太low了

我举个简单的例子，模拟一下这个现象，说明参数化的存存储过程是怎么写的，存在哪些问题，又如何解决parameter sniff问题，

先创建一个测试环境：

create table ParameterSniffProblem ( id int identity(1,1), CustomerId int, OrderId int, OrederStatus int, CreateDate Datetime, Remark varchar(200) ) declare @i int = 0 while @i<500000 begin INSERT INTO ParameterSniffProblem values (@i%10000,@i,RAND()*10,GETDATE()-RAND()*100,NEWID()) set @i=@i+1 end --假如某一个客户有非常多的订单，模拟数据分布不均匀的情况 INSERT INTO ParameterSniffProblem values (6666,RAND()*100000,1,GETDATE()-RAND()*100,NEWID()) GO 100000 --创建正常的索引 CREATE CLUSTERED INDEX IDX_CreateDate on ParameterSniffProblem(CreateDate )

CREATE INDEX IDX_CustomerId ON ParameterSniffProblem(CustomerId)

参数化存储过程的写法：

在编写存储过程的时候，我们一般建议采用参数化的写法，目的是为了减少存储过程的编译和加强执行计划缓存的重用

大概是这样子的

CREATE PROCEDURE [dbo].ParameterSniffTest ( @p_CustomerId int, @p_Status int, @p_FromDate datetime, @p_ToDate datetime ) AS BEGIN SET NOCOUNT ON DECLARE @Parm NVARCHAR(MAX), @sqlcommand NVARCHAR(MAX) = N'' SET @sqlcommand = 'SELECT * FROM ParameterSniffProblem WHERE 1=1' IF(@p_CustomerId IS NOT NULL) SET @sqlcommand = CONCAT(@sqlcommand,'AND CustomerId=@p_CustomerId ') IF(@p_Status IS NOT NULL) SET @sqlcommand = CONCAT(@sqlcommand,'AND OrederStatus=@p_Status ') IF(@p_FromDate IS NOT NULL) SET @sqlcommand = CONCAT(@sqlcommand,'AND CreateDate>=@p_FromDate ') IF(@p_ToDate IS NOT NULL) SET @sqlcommand = CONCAT(@sqlcommand,'AND CreateDate<=@p_ToDate ') SET @Parm= '@p_CustomerId int, @p_Status int, @p_FromDate datetime, @p_ToDate datetime ' EXEC sp_executesql @sqlcommand,@Parm, @p_CustomerId = @p_CustomerId, @p_Status = @p_Status, @p_FromDate = @p_FromDate, @p_ToDate = @p_ToDate END GO

Parameter Sniff问题：

这就潜在一个parameter sniff问题，

比如我查询用户ID=100的订单信息，一个正常的分布的数据，存储过程第一次编译，这个执行计划完全没有问题，

SQL Server中参数化SQL写法遇到parameter sniff ，导致不合理执行计划重用的快速解决方法1

如果我接着改变参数执行查询用户6666的信息，一个分布及其不均匀的数据，但是因为重用上面缓存的执行计划，就出现parameter sniff问题了，这个执行计划显然是不合理的

IO就不看了，刻意造的例子

SQL Server中参数化SQL写法遇到parameter sniff ，导致不合理执行计划重用的快速解决方法2

如果我清空执行计划缓存，重新执行上述查询，因为有了重编译，执行计划就是不这个样子，对于CustomerID=6666这个参数来说，显然走全表扫描代价要更小一点

SQL Server中参数化SQL写法遇到parameter sniff ，导致不合理执行计划重用的快速解决方法3

想必这是一个开发中常见的问题给，我们参数化SQL就是为了让不同参数的查询重用执行计划，但是很不幸，数据分布不均匀的时候，重用执行计划恰恰又给数据库造成了伤害，例中，如果是正常参数重用了分布较多数据的执行计划，比如命名可以用到索引，结果是表扫描，后果会更严重。

那么，既想要尽可能的重用执行计划，又要避免因为执行计划重用产生parameter sniff问题，怎么办？

我们知道问题在于@p_CustomerId身上，那么可不可以对有可能产生parameter sniff问题的@p_CustomerId不做参数化，直接拼凑在SQL中，如果@p_CustomerId变化了就重编译SQL，也就是对传入进来的@p_CustomerId重编译

如果是@p_CustomerId不变，其他参数有变化，比如这里时间字段的变化，还可以享受参数化带来的执行计划重用的好处也就是这样处理 @p_CustomerId这个参数，直接把@p_CustomerId以字符串的方式平凑在SQL语句中，这样的话，就相当于即席查询了，不通过参数化的方式给CustomerId这个查询条件字段赋值

IF(@p_CustomerId IS NOT NULL) SET @sqlcommand = CONCAT(@sqlcommand,'AND CustomerId= ',@p_CustomerId)

这样再去执行存储过程的时候，

带入@p_CustomerId=1的时候，执行IDX_CustomerId的index seek

SQL Server中参数化SQL写法遇到parameter sniff ，导致不合理执行计划重用的快速解决方法4