主页 / dba / 问题 / 340540
Accepted
J. Mini
Asked: 2024-06-27 04:15:53 +0800 CST

为什么向我的表中添加空的列存储索引会导致执行计划忽略位图？

  • 772

问题

我使用的是标准版 SQL Server 2019 机箱。考虑到版本，兼容级别应该不相关（标准版上不允许使用与级别相关的批处理模式功能）。在此机箱上，我一直在尝试在临时表连接到其他表之前向其添加一个空的非聚集筛选列存储索引，从而允许具有所述连接的查询使用批处理模式，尽管不使用任何列存储索引。当我这样做时，我经常在以前未使用批处理模式的查询中发现以下行为：

  • 现在使用批处理模式的执行计划部分运行速度更快。特别是，与没有批处理模式部分的计划相比，窗口函数变得令人难以置信。
  • 具有批处理模式部分的计划的成本总是比没有批处理模式部分的查询低得多，即使它们运行的​​时间更长。
  • 尽管每个操作员在具有批处理模式步骤的计划中的两个线程上均匀运行，但 Gather Streams 有时会变得更慢。
  • 之前，为了节省扫描成本，在一个表上构建了一个位图，然后通过在扫描中传递位图的哈希探测来哈希连接另一个表，这样的查询会变得慢得多。与索引扫描中的位图不同，它们在扫描后有一个非常昂贵的 Filter 运算符。

对我列表中的最后一个观察结果有什么解释吗？更重要的是，我如何防止引入批处理模式删除优秀的位图？这个粗体点就是我想要在这里回答的。如果我可以在查询的其他地方（例如在窗口函数中）同时拥有位图和批处理模式的优秀优势，那么我的查询就会快得多。我承认我可能可以更好地索引表，但我对原始哈希连接已经很满意了。

可重现的例子

Erik Darling 非常正确地要求我粘贴该计划，但我认为我应该做得更好并给出一个可重现的示例。以下使用StackOverflow2010 数据库。如果您愿意，可以将其加载到 docker 容器中。

SET STATISTICS XML OFF;

USE StackOverflow2010;

DROP TABLE IF EXISTS #IDs;

SELECT TOP (10000)
  Id
INTO
  #IDs
FROM
  Comments;

ALTER TABLE #IDs ADD CONSTRAINT RowStorePK
PRIMARY KEY CLUSTERED (Id);

SET STATISTICS XML ON;
  
;WITH [NumberedPosts] AS
(
  SELECT
    PostID
    ,ROW_NUMBER() OVER (PARTITION BY PostID ORDER BY Score) RN
  FROM
    Comments
  WHERE
    PostID IN (SELECT Id FROM #IDs)
)
SELECT
  PostID
FROM
  NumberedPosts
WHERE
  RN = 1;

/*
  Hack to get batch mode for these queries despite 
  my real machine being on Standard Edition.
*/
CREATE NONCLUSTERED COLUMNSTORE INDEX ColStoreNonClust ON #IDs (ID)
WHERE ID = 1 AND ID = 2;

-- Same query again.

;WITH [NumberedPosts] AS
(
  SELECT
    PostID
    ,ROW_NUMBER() OVER (PARTITION BY PostID ORDER BY Score) RN
  FROM
    Comments
  WHERE
    PostID IN (SELECT Id FROM #IDs)
)
SELECT
  PostID
FROM
  NumberedPosts
WHERE
  RN = 1;

此计划是在创建列存储索引之前执行的。它使用位图。此计划是在创建列存储索引之后执行的。它不使用位图，并且运行速度比使用位图的查询慢得多。

我已经多次见过这种情况，我确信这是一种必须有一些一般解释或预防步骤的模式。

sql-server

1 个回答

  1. Best Answer

    我怎样才能防止引入批处理模式删除优秀的位图？

    简短回答

    不行。你需要从标准版升级。

    长答案

    它们可能都被称为位图，但是行模式位图与批模式位图有很大不同。

    批处理模式位图仅由批处理模式哈希连接创建。它们最初仅用于批处理模式列存储扫描。

    当添加混合模式计划（包含行和批处理模式运算符）时，包含特定支持以评估行模式过滤器运算符中的不同批处理模式位图结构。

    这种支持是使一切正常运转的最低要求。诸如将过滤器作为残差（“非 sarg”）谓词下推到扫描中，或甚至更深地推入存储引擎之类的优化根本就没有实现。

    他们本可以继续实施这些优化，从而获得您想要的结果。但实际上，他们决定投入时间和精力来创建Rowstore 上的批处理模式(BMoR)。

    行存储上的批处理模式支持批处理模式执行分析工作负载，而无需列存储索引。此功能支持批处理模式执行和磁盘堆和 B 树索引的位图筛选器。行存储上的批处理模式支持所有现有的启用批处理模式的运算符。

    如上文文档中简要所述（重点已添加），BMoR 允许在行存储索引的批处理模式扫描中评估批处理模式位图。当然，除此之外，它还提供许多其他一般好处。

    遗憾的是，BMoR 是一项高级性能功能，因此在标准版中不可用。

    如果在标准上使用批处理模式哈希连接，则会在过滤器中看到一个位图（除非目标是列存储并以批处理模式访问）。

    如果您使用行模式哈希连接，您将获得一种可以深入存储引擎的不同类型的位图（在有限的情况下）。

    在 Standard 上，您无法同时兼顾两者的优点。您既可以使用批处理模式哈希连接和卡住的过滤器，也可以使用行模式哈希连接和可以下推的位图。

    正如问题中所提到的，兼容性级别不是一个因素。

    警告

    以上任何内容都不是说您不能在计划的某些部分使用批处理模式，而在计划的其他部分使用行模式。它仅限于与哈希连接相关的位图行为的精确点。

    优化器通常能够选择具有行模式哈希连接加位图的计划，同时还可以在批处理模式下运行其他运算符（如排序或窗口聚合） ​​。它还能够不执行您想要的任何特定情况。这一切都取决于成本估算和启发式方法。

    如果您可以使用支持的方法生成所需的计划形状，则可以使用查询存储或计划指南强制执行该计划。总的来说，祝您好运。

    行模式哈希连接与位图以及批处理模式操作 从技术上讲可行，但并不常见

    在您的示例中，优化器极不可能为您想要的运算符选择批处理模式，同时为哈希连接选择行模式。上面的计划只是为了表明它是优化器在技术上有效的输出。它不是使用支持的技术生成的。

    带合并连接的位图

    以下经过调整的演示查询通过合并连接生成所需的位图：

    -- Hack empty CS index present on #IDs
WITH 
    NumberedPosts AS
    (
        SELECT
            C.PostId,
            rn = 
                ROW_NUMBER() OVER (
                    PARTITION BY C.PostId
                    ORDER BY C.Score) 
        FROM dbo.Comments AS C
        WHERE
            PostID IN 
            (
                SELECT 
                    Id + 0 - 0 
                FROM #IDs
            )
    )
SELECT
    NP.PostId
FROM NumberedPosts AS NP
WHERE
    NP.rn = 1
OPTION 
(
    MERGE JOIN
);

    使用位图和批处理模式运算符进行合并连接

    不使用 BMoR。合并连接上方的所有运算符都是批处理模式。非常有选择性的行模式过滤器被推入注释的存储引擎并行扫描中。

    • 7

相关问题