• 企业400电话
  • 微网小程序
  • AI电话机器人
  • 电商代运营
  • 全 部 栏 目

    企业400电话 网络优化推广 AI电话机器人 呼叫中心 网站建设 商标✡知产 微网小程序 电商运营 彩铃•短信 增值拓展业务
    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 @i500000
    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问题

        遇到问题,解决方法有很多种,以最小的代价解决问题才是王道。

    您可能感兴趣的文章:
    • 浅析SQL Server 聚焦索引对非聚集索引的影响
    • MySQL中主键索引与聚焦索引之概念的学习教程
    • SQLSERVER中得到执行计划的两种方式
    • SqlServer 执行计划及Sql查询优化初探
    • 浅析SQL Server中的执行计划缓存(下)
    • 浅析SQL Server中的执行计划缓存(上)
    • 强制SQL Server执行计划使用并行提升在复杂查询语句下的性能
    • 浅析SQL Server的聚焦使用索引和查询执行计划
    上一篇:MySQL 及 SQL 注入与防范方法
    下一篇:SQL Server 2016正式版安装配置过程图文详解
  • 相关文章
  • 

    © 2016-2020 巨人网络通讯 版权所有

    《增值电信业务经营许可证》 苏ICP备15040257号-8

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