SQL Server中参数化SQL写法遇到parameter sniff ，导致不合理执行计划重用的快速解决方法
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的订单信息，一个正常的分布的数据，存储过程第一次编译，这个执行计划完全没有问题，
如果我接着改变参数执行查询用户6666的信息，一个分布及其不均匀的数据，但是因为重用上面缓存的执行计划，就出现parameter sniff问题了，这个执行计划显然是不合理的
io就不看了，刻意造的例子
如果我清空执行计划缓存，重新执行上述查询，因为有了重编译，执行计划就是不这个样子，对于customerid=6666这个参数来说，显然走全表扫描代价要更小一点
想必这是一个开发中常见的问题给，我们参数化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
带入@p_customerid=6666的时候，重编译，执行计划是全表扫描，避免重用上面生成的执行计划，造成不合理的执行方式对效率以及数据库服务器资源的消耗
这样会尽可能的减少parameter sniff问题带来的影响，当缓存了@p_customerid=1的执行计划的时候，再次传入@p_customerid=1，其他条件有较小的变化，比如时间字段上有改动，依然可以重用缓存的执行计划，避免重编译带来的影响
结论：
这种方式于处理parameter sniff问题，当然不是完美的，肯定也有问题，我当然知道一旦@p_customerid不同就要重编译
肯定会因为@p_customerid参数值不同，这样的话，不可避免地增加了重编译的机会，
但是却不会因为不合理的执行计划重用，带来的parameter sniff问题
要知道一旦产生parameter sniff问题，大量的查询用到不合理的执行计划，会对整个服务器产生非常严重的影响，比如可能会产生大量的io等
同时存在一个好处，比如第一次传入@p_customerid=1，
再次传入@p_customerid=1，其他条件有较小的变化，比如时间字段上有改动，依然可以重用缓存的执行计划，避免重编译带来的影响当然我这里只是一个简单的例子，实际应用中远远比这个复杂
比如分布的特别的多的数据有两个特点，第一分布的标示不仅仅只有一个，第二分布不均的数据是动态的，有可能第一季度是a这部分数据占据大多数，有可能是第二季度b数据占绝大多数
所以很难采用plan guide的方式解决parameter sniff问题
这种方式可以在一定程度上也能够重用缓存的执行计划，可以减少（但不可避免）重编译的次数
同时，这种方式与拼凑一个sql字符串执行的即席查询方式相比，同时还可以利用参数化带来的其他好处，比如sql注入等等
总结：
parameter sniff问题的解决方式有很多，不一一啰嗦了
最典型的就是强制重编译，
或者使用exec执行一个拼凑出来的字符串，这种方式属于adhoc查询
或者查询提示，
或者是使用本地变量，
或者使用plan guide等等等等，
每种方式都有他的局限性，至少到目前为止，还没有一种十全十美的方式来解决parameter sniff问题
遇到问题，解决方法有很多种，以最小的代价解决问题才是王道。