SQL Server中参数化SQL写法遇到parameter sniff ，导致不合理执行计划重用的快速解决方法
6 浏览量
2020-12-15
04:20:59
上传
评论
收藏 192KB PDF 举报
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,
weixin_38608875
- 粉丝: 3
- 资源: 992
评论0