主页 / dba / 问题 / 130751
Accepted
Royi Namir
Asked: 2016-02-29 22:24:55 +0800 CST

除非选项（重新编译），否则不使用索引 SEEK？

  • 772

（问题从SO转移）

我有一个带有聚簇索引的表（虚拟数据）包含 2 列：

在此处输入图像描述

现在我运行这两个查询：

declare 
@productid int =1 , 
@priceid  int = 1




SELECT productid,
       t.priceID
FROM   Transactions AS t
WHERE  (productID = @productid OR @productid IS NULL)
       AND (priceid = @priceid OR @priceid IS NULL)  


SELECT productid,
       t.priceID
FROM   Transactions AS t
WHERE  (productID = @productid)
       AND (priceid = @priceid)

两个查询的实际执行计划是：

在此处输入图像描述

如您所见，第一个使用 SCAN，第二个使用 SEEK。

然而 - 添加OPTION (RECOMPILE)到第一个查询，使执行计划也使用 SEEK：

在此处输入图像描述

DBA 聊天室的朋友告诉我：

在您的查询中，@productid=1，这意味着 (productID=@productID OR @productID IS NULL) 可以简化为 (productID=@productID)。前者需要扫描才能使用@productID 的任何值，后者可以使用搜索。因此，当您使用 RECOMPILE 时，SQL Server 将查看您在 @productID 中实际拥有的值并为其制定最佳计划。对于@productID 中的非空值，搜索是最好的。如果@productID 的值未知，则计划必须适合@productID 中的任何可能值，这将需要扫描。请注意：OPTION (RECOMPILE) 将在您每次运行时强制重新编译计划，这将在每次执行时增加几毫秒。虽然这只是一个问题，如果查询运行非常频繁。

还 ：

如果@productID 为空，您会寻求什么值？答：无所求。所有值都符合条件。

我知道这会OPTION (RECOMPILE)强制 SQL Server 查看参数的实际值，并查看它是否可以使用它进行 SEEK。

但是现在我失去了提前编译的好处。

问题

恕我直言，扫描只会在参数为空时发生。
没关系——让 SQL SERVER 为 SCAN 创建一个执行计划。
但是，如果 SQL Server 发现我多次使用值运行此查询：1,1，那么为什么它不创建另一个执行计划并为此使用 SEEK？

AFAIK-SQL 为命中率最高的查询创建执行计划。

  • 为什么 SQL SERVER 不保存执行计划：

    @productid int =1 , @priceid int = 1

（我用这些值运行了很多次）

  • 是否可以强制 SQL 保留该执行计划（使用 SEEK）以供将来调用？

完整的创建表脚本+数据

index sql-server-2008-r2

1 个回答

  1. Best Answer

    总结我们聊天室讨论的一些要点：

    一般来说，SQL Server为每个语句缓存一个计划。该计划必须对所有可能的未来参数值有效。

    不可能为您的查询缓存查找计划，因为如果例如@productid为空，该计划将无效。

    在未来的某个版本中，SQL Server 可能支持单个计划，该计划根据运行时参数值在扫描和查找之间动态选择，但这不是我们今天拥有的。

    一般问题类

    您的查询是一种模式的示例，该模式被称为“全部捕获”或“动态搜索”查询。有多种解决方案，每种都有自己的优点和缺点。在现代版本的 SQL Server (2008+) 中，主要选项有：

    • IF积木
    • OPTION (RECOMPILE)
    • 动态 SQL 使用sp_executesql

    关于该主题的最全面的工作可能是由 Erland Sommarskog 完成的，它包含在本答案末尾的参考资料中。无法摆脱所涉及的复杂性，因此有必要花一些时间尝试每个选项以了解每种情况下的权衡。

    IF积木

    为了说明IF问题中特定情况的块解决方案：

    IF @productid IS NOT NULL AND @priceid IS NOT NULL
BEGIN
    SELECT 
        T.productID,
        T.priceID
    FROM dbo.Transactions AS T
    WHERE
        T.productID = @productid
        AND T.priceID = @priceid;
END;
ELSE IF @productid IS NOT NULL
BEGIN
    SELECT 
        T.productID,
        T.priceID
    FROM dbo.Transactions AS T
    WHERE
        T.productID = @productid;
END;
ELSE IF @priceid IS NOT NULL
BEGIN
    SELECT 
        T.productID,
        T.priceID
    FROM dbo.Transactions AS T
    WHERE
        T.priceID = @priceid;
END;
ELSE
BEGIN
    SELECT 
        T.productID,
        T.priceID
    FROM dbo.Transactions AS T;
END;

    这包含针对两个参数（或局部变量）中的每一个的四种可能的 null 或非 null 情况的单独语句，因此有四个计划。

    参数嗅探存在潜在问题，可能需要OPTIMIZE FOR对每个查询进行提示。请参阅参考资料部分来探索这些类型的微妙之处。

    重新编译

    如上所述，在问题中，您还可以添加OPTION (RECOMPILE)提示以在每次调用时获得新计划（搜索或扫描）。鉴于您的情况下调用频率相对较慢（平均每十秒一次，编译时间为亚毫秒），此选项似乎适合您：

    SELECT
    T.productID,
    T.priceID
FROM dbo.Transactions AS T
WHERE
    (T.productID = @productid OR @productid IS NULL)
    AND (T.priceID = @priceid OR @priceid IS NULL)
OPTION (RECOMPILE);

    还可以以创造性的方式组合上述选项的功能，以充分利用每种方法的优点，同时最大限度地减少缺点。确实没有捷径可以详细了解这些东西，然后在实际测试的支持下做出明智的选择。

    延伸阅读

    • 10

相关问题