主页 / dba / 问题 / 260639
Accepted
Data Dill
Asked: 2020-02-27 07:13:16 +0800 CST

为什么这个存储过程会导致聚集索引扫描，但在使用 OPTION RECOMPILE 时会查找？

  • 772

我想我可能会根据我的研究知道答案，但我正在寻找关于引擎如何/为什么按照它的方式编译计划的确认

传入参数：@ID int ,@OtherID INT

SELECT  b.Column1
        ,b.Column2
        ,b.Column3
        ,b.Column4
        ,b.Column5
        ,c.Column1
        ,b.Column1
        ,e.Column1
FROM    Table1 AS b 
        inner join Table2 AS t
        on b.ID = t.ID
        left join [LINKED SERVER].[DB].dbo.Table3 as c
        on b.ID = c.ID
        left join Table4 AS e
        on b.ID= e.ID
where   (b.ID = @ID or @ID= 0)
        And b.ID = @OtherID
        And b.ID IS NOT NULL
        and e.ID = 1

现在我已经确定索引扫描的原因是因为这行：where (b.ID = @ID or @ID= 0). 更具体地说，@ID = 0。为了进一步澄清，该 ID 字段的 0 不作为基础表中的值存在，这只是开发人员所做的事情，以允许用户通过传入来拉回所有结果0 到参数，然后检查该参数是否为 0，因此结果会拉回更多行（通常，您只会返回 1-3 个结果）。

现在，非常奇怪的是，如果我添加OPTION RECOMPILE，引擎当然能够以开销（编译时间）为代价创建一个更好的计划：

在此处输入图像描述

我想知道这怎么可能。根据我在网上阅读的内容，通过 using OPTION RECOMPILE，引擎将用传递给参数的实际值替换该值，并且可以很容易地看到 @ID 1234 不等于 0。但是，如果您不使用OPTION RECOMPILE引擎将获取记录的总数，即 120,000，然后将其除以不同可能性的总数，即 107,000。这会返回大约 1.1 个估计行，我通过查看具有索引扫描的计划的估计属性来确认这一点，但是如果估计正确，为什么引擎会继续索引扫描呢？为了确定，我什至更新了统计数据。

在此处输入图像描述

sql-server optimization

2 个回答

  1. Best Answer
    b.ID = @ID OR @ID = 0

    优化器必须生成一个带有索引扫描的计划，因为该计划是缓存和重用的。

    在随后的执行中，该参数@ID可能为零。在这种情况下，索引搜索没有任何价值，因为没有任何ID价值可以搜索。其他时候，将为 提供一个非零值@ID，但缓存计划必须为所有可能的参数值正确工作。

    使用OPTION (RECOMPILE)时，参数嵌入优化(PEO) 表示每次执行时使用当前值@ID代替参数，并且不缓存任何计划。

    说@ID是 1234。在 PEO 之后，优化器看到：

    b.ID = 1234 OR 1234 = 0

    矛盾检测逻辑将其简化为：

    b.ID = 1234

    ...这可以在ID.

    如需进一步阅读，请参阅我的文章Parameter Sniffing, Embedding, and the RECOMPILE Options。

    • 2

  2. 您的问题是可选条件(b.ID = @ID or @ID= 0)

    如果您不想使用OPTION(RECOMPILE)，则应根据以下条件拆分查询：

    IF @ID = 0 BEGIN
    SELECT b.Column1, 
           b.Column2, 
           b.Column3, 
           b.Column4, 
           b.Column5, 
           c.Column1, 
           b.Column1, 
           e.Column1
    FROM Table1 AS b
         INNER JOIN Table2 AS t ON b.ID = t.ID
         LEFT JOIN [LINKED SERVER].[DB].dbo.Table3 AS c ON b.ID = c.ID
         LEFT JOIN Table4 AS e ON b.ID = e.ID
    WHERE b.ID = @OtherID
          AND b.ID IS NOT NULL
          AND e.ID = 1;
END ELSE BEGIN
    SELECT b.Column1, 
           b.Column2, 
           b.Column3, 
           b.Column4, 
           b.Column5, 
           c.Column1, 
           b.Column1, 
           e.Column1
    FROM Table1 AS b
         INNER JOIN Table2 AS t ON b.ID = t.ID
         LEFT JOIN [LINKED SERVER].[DB].dbo.Table3 AS c ON b.ID = c.ID
         LEFT JOIN Table4 AS e ON b.ID = e.ID
    WHERE b.ID = @ID
          AND b.ID = @OtherID
          AND b.ID IS NOT NULL
          AND e.ID = 1;
END
    • 1

相关问题