主页 / dba / 问题 / 187576
Accepted
Erik Darling
Asked: 2017-10-04 16:09:06 +0800 CST

SQL Server 2017：批处理模式自适应联接如何工作？

  • 772

批处理模式自适应联接是2017 查询处理器中一系列功能的一部分，其中包括：

那么自适应联接是如何工作的呢？

sql-server sql-server-2017

1 个回答

  1. Best Answer

    批处理模式自适应联接

    对于批处理模式自适应连接，目标是在编译时不将连接选择固定到特定类型。

    如果可用，自适应联接允许优化器在运行时根据行阈值在嵌套循环联接和哈希联接之间进行选择。

    此时，不考虑合并连接。纯粹的猜测是，需要对数据进行排序，或者需要将排序注入计划中会在更改查询过程时增加太多开销。

    批处理模式自适应联接何时发生？

    此时，批处理模式查询处理需要存在 ColumnStore 索引。它们还需要一个连接和一个允许选择嵌套循环或哈希连接的索引。

    我如何知道我的加入是否是自适应的？

    自适应联接的查询计划非常独特。

    坚果

    Adaptive Join 运算符是 SQL Server 2017 的新增功能，目前在实际执行计划中具有以下属性。

    坚果

    • 物理操作：自适应连接

    • 实际连接类型：将是哈希匹配或嵌套循环

    • 自适应阈值行：表示连接类型切换到哈希匹配时的临界点

    • 是否自适应：适用于自适应连接

    • 估计的连接类型：不言自明！

    在估计或缓存的计划中，信息相当少：

    坚果

    最值得注意的是，缺少实际连接类型。

    是什么破坏了批处理模式自适应连接？

    为了监控这一点，有一个名为 的扩展事件会话adaptive_join_skipped，它有以下原因跳过批处理模式自适应连接：

    • eajsrExchangeTypeNotSupported
    • eajsrHJorNLJNotFound
    • eajsrInvalidAdaptiveThreshold
    • eajsrMultiConsumerSpool
    • eajsrOuterCardMaxOne
    • eajsrOuterSideParallelMarked
    • eajsrUnMatchedOuter

    除此之外，批处理模式自适应连接可能会因为其他原因而被跳过。以这两个查询为例：

    /*Selecting just integer data*/
SELECT uc.Id, uc.Reputation, p.Score
FROM   dbo.Users_cx AS uc
JOIN   dbo.Posts AS p
ON p.OwnerUserId = uc.Id
WHERE  uc.LastAccessDate >= '20160101';

/*Selecting one string column from Users*/
SELECT uc.Id, uc.DisplayName, uc.Reputation, p.Score
FROM   dbo.Users_cx AS uc
JOIN   dbo.Posts AS p
ON p.OwnerUserId = uc.Id
WHERE  uc.LastAccessDate >= '20160101';

    它们是相同的，只是第二个查询选择DisplayName了类型为NVARCHAR(40).

    坚果

    第二个查询跳过了批处理模式自适应连接，但没有将任何原因记录到 XE 会话中。字符串数据似乎仍然是 ColumnStore 索引的死敌。

    还有其他查询模式无法获得自适应连接，也不会触发事件。

    一些例子：

    • CROSS APPLY与TOP

    • OUTER APPLY

    eajsrExchangeTypeNotSupported

    触发此事件的一件事似乎是存在 Repartition Streams 运算符。在此查询中，分区类型为哈希匹配。特别感谢星际天体伪装成一个卑微的博客作者保罗·怀特，提出了这个奇怪的问题。

    SELECT uc.Id, uc.Reputation, CONVERT(XML, CONVERT(NVARCHAR(10), p.Score)).value('(xml/text())[1]', 'INT') AS [Surprise!]
FROM   dbo.Users_cx AS uc
JOIN   dbo.Posts AS p
ON p.OwnerUserId = uc.Id
WHERE  uc.LastAccessDate <= '2009-08-01'
OPTION ( USE HINT ( 'ENABLE_PARALLEL_PLAN_PREFERENCE' ));
GO

    eajsrHJorNLJNotFound

    尚未有任何查询触发此 XE。什么不起作用：

    • 合并加入提示

    • 排除连接类型的查询模式，例如 Hash 和 Merge 连接至少需要一个相等谓词。写入连接>=并且<=不会触发事件。

    eajsrInvalidAdaptiveThreshold

    此事件可由各种TOP、FAST N和OFFSET/FETCH查询触发。这里有些例子：

    SELECT uc.Id, uc.DisplayName, uc.Reputation, p.Score
FROM   dbo.Users_cx AS uc
INNER JOIN   dbo.Posts AS p
ON p.OwnerUserId = uc.Id
WHERE  uc.LastAccessDate <= '2008-01-01'
OPTION ( FAST 1);
GO 

SELECT TOP 1 uc.Id, uc.DisplayName, uc.Reputation, p.Score
FROM   dbo.Users_cx AS uc
INNER JOIN   dbo.Posts AS p
ON p.OwnerUserId = uc.Id
WHERE  uc.LastAccessDate <= '2008-01-01';
GO

    在某些情况下，它们也可以由分页式查询触发：

    WITH pages
AS ( SELECT TOP 100 uc.Id, ROW_NUMBER() OVER ( ORDER BY uc.Id ) AS n
     FROM   dbo.Users_cx AS uc ),
     rows
AS ( SELECT TOP 50 p.Id
     FROM   pages AS p
     WHERE  p.n > 50 )
SELECT u.Id, u.Reputation
FROM   pages AS p
JOIN   dbo.Users AS u
ON p.Id = u.Id;

    eajsrMultiConsumerSpool

    尚无已知的查询模式触发此事件。

    到目前为止还没有触发它的原因：

    • 递归 CTE

    • 分组集/多维数据集/汇总

    • PIVOT 和 UNPIVOT

    • 窗口函数

    eajsrOuterCardMaxOne

    几种不同类型的查询触发了此事件。带有 a 的派生连接TOP 1，以及与 WHERE 子句组合的连接，该子句在唯一列上具有相等谓词：

    SELECT uc.Id, uc.Reputation, p.Score
FROM   dbo.Users_cx AS uc
JOIN   (SELECT TOP 1 p2.OwnerUserId, p2.Score FROM dbo.Posts AS p2 ORDER BY Id) AS p
ON p.OwnerUserId = uc.Id
WHERE  uc.LastAccessDate <= '2009-08-01';
GO 

SELECT p.Id, p.ParentId, p.OwnerUserId
FROM dbo.Posts AS p
JOIN dbo.Users_cx AS uc
ON uc.Id = p.OwnerUserId
WHERE p.Id = 17333;
GO

    eajsrOuterSideParallelMarked

    可以触发此事件的一种查询是递归 CTE。

    WITH postparent AS 
    (
    SELECT p.Id, p.ParentId, p.OwnerUserId 
    FROM dbo.Posts_cx AS p
    WHERE p.Id = 17333

        UNION ALL

    SELECT p2.Id, p2.ParentId, p2.OwnerUserId
    FROM postparent pp
    JOIN dbo.Posts_cx AS p2
    ON pp.Id = p2.ParentId
    )
    SELECT pp.Id, pp.ParentId, pp.OwnerUserId, u.DisplayName
    FROM postparent pp
    JOIN dbo.Users AS u
    ON u.Id = pp.OwnerUserId
    ORDER BY pp.Id
    OPTION (USE HINT('ENABLE_PARALLEL_PLAN_PREFERENCE'));

    这里的原因似乎是 CTE 的递归部分导致计划中的串行区域不允许批处理模式自适应连接选择。

    eajsrUnMatchedOuter

    这是最常见的，并且似乎在将索引用于无法支持查找的连接时发生。例如，此查询会导致 Key Lookup：

    SELECT uc.Id, uc.Reputation, p.Score, p.LastActivityDate 
FROM   dbo.Users_cx AS uc
JOIN   dbo.Posts AS p
ON p.OwnerUserId = uc.Id
WHERE  uc.LastAccessDate >= '20080101'
       AND uc.DisplayName = 'harrymc'
       AND p.Score > 1;

    坚果

    生成的查询选择行模式嵌套循环连接来执行键查找和表连接，这会触发事件。

    另一个示例是跳过窄非聚集索引以支持 PK/CX 的查询。在这种情况下，PK/CX 不以 开头OwnerUserId，因此唯一的连接选择是哈希连接。

    在这两种情况下，“不匹配的外部”似乎表明选择的索引不足以覆盖我们的查询。

    SELECT uc.Id, uc.Reputation, p.*
FROM   dbo.Users_cx AS uc
JOIN   dbo.Posts AS p
ON p.OwnerUserId = uc.Id
WHERE  uc.LastAccessDate >= '20160101';

    批处理模式自适应联接是否适用于多个联接？

    是的，但在撰写本文时，似乎存在一个限制：

    从一个列存储索引连接到多个行存储索引将产生多个自适应连接，而多个列存储索引之间的连接将不是自适应的。

    例如，这两个查询

    SELECT uc.Id, uc.Reputation, p.Score
FROM   dbo.Users_cx AS uc
JOIN   dbo.Posts AS p
ON p.OwnerUserId = uc.Id
JOIN dbo.Comments AS c
ON c.PostId = p.Id
AND  c.UserId = uc.Id
WHERE  uc.LastAccessDate >= '20160101';

SELECT uc.Id, uc.Reputation, p.Score
FROM   dbo.Users_cx AS uc
JOIN   dbo.Posts AS p
ON p.OwnerUserId = uc.Id
JOIN dbo.Comments_cx AS c
ON c.PostId = p.Id
AND  c.UserId = uc.Id
WHERE  uc.LastAccessDate >= '20160101';

    第一个查询将一个 ColumnStore 索引（在用户上）连接到两个在 Posts 和 Comments 上的 Row Store 索引。这会产生两个自适应连接运算符。

    第二个查询将两个列存储表（用户和评论）连接到一个行存储表（帖子），并产生一个自适应连接。

    坚果

    批处理模式自适应联接是否有任何开销？

    是的，所有批处理模式自适应加入计划都会获得内存授权。嵌套循环连接并非总是如此，除非它们接收到嵌套循环预取优化。如果计划需要基于行阈值的内存连接，则内存授权将支持哈希连接。

    • 12

相关问题