主页 / dba / 问题 / 187582
Accepted
Erik Darling
Asked: 2017-10-04 17:22:12 +0800 CST

SQL Server 2017：批处理模式内存授予反馈如何工作？

  • 772

批处理模式内存授予反馈是2017 查询处理器中的一系列功能的一部分，其中包括：

那么批处理模式内存授予反馈是如何工作的呢？

sql-server sql-server-2017

1 个回答

  1. Best Answer

    什么是内存授予反馈？

    批处理模式内存授予反馈尝试调整查询的内存授予大小，纠正高估和低估。

    当运行需要内存授予的查询时，优化器将要求授予其认为可以将所有行操作保留在内存中的大小。

    通常需要内存授予的事情：

    • 排序
    • 哈希连接

    当请求和授予过多内存时，并发性可能会因此受到影响。内存是一种有限的资源，并不是所有的东西都可以一直使用它。没有无限访问此类资源这样的事情。

    当请求的内存太少时，查询可能会溢出到磁盘。看，没有人希望他们的查询溢出任何地方。磁盘是可怕的。

    魔法是如何运作的？

    当需要内存授予的计划执行并缓存时，将重新计算运行该计划所需的实际内存，并相应地更新计划信息。现在，它需要 ColumnStore 索引的存在才能实现 Batch 执行模式。

    如果魔法不那么神奇怎么办？

    此功能确实有一个终止点，它将回退到原始内存授予。如果需要不断重新计算的查询运行，我们的神奇功能将放弃。最终。在撰写本文时，我还没有关于何时退出的所有实施细节。

    我怎么知道它是否有效？

    您可以使用扩展事件：

    坚果

    您还可以在对实际执行计划中的多次运行进行常规查询调整期间观察到它。

    你能给我举个例子吗？

    当然！这是一个存储过程。在适当的情况下，它会请求不正确的内存授予。

    CREATE OR ALTER PROCEDURE dbo.LargeUnusedGrant (@OwnerUserId INT)
AS 
BEGIN

    SELECT TOP 200 *
    FROM dbo.Posts_cx AS p
    WHERE p.OwnerUserId = @OwnerUserId
    AND p.PostTypeId = 1
    ORDER BY p.Score DESC, p.Id DESC;

END;
GO

    查询计划对选择运算符有警告。

    坚果

    真是悲哀！我们的查询要求太多内存。

    坚果

    第二次执行时，警告将消失。

    我听说过一些偷偷摸摸的把戏……

    Itzik Ben-Gan和Niko Neugebauer都针对需要批处理模式执行的功能和运算符提出了解决方法。

    1. WHERE使用包含 0 行的子句创建过滤的非聚集 ColumnStore 索引
    2. 使用多余的 Left Join 将您的表连接到一个临时表，该表上有一个 Clustered ColumnStore 索引，没有任何行

    这两种方法都是有效的解决方法，可以让它发挥作用！

    创建一个空的非聚集 ColumnStore 索引：

    /*Itzik's Trizik*/
CREATE NONCLUSTERED COLUMNSTORE INDEX ncci_helper
    ON dbo.Posts
(
    Id,
    AcceptedAnswerId,
    AnswerCount,
    ClosedDate,
    CommentCount,
    CommunityOwnedDate,
    CreationDate,
    FavoriteCount,
    LastActivityDate,
    LastEditDate,
    LastEditorDisplayName,
    LastEditorUserId,
    OwnerUserId,
    ParentId,
    PostTypeId,
    Score,
    ViewCount,
    IsHot )
    WHERE ( Id = -2147483647 AND Id = 2147483647) -- eez impossible!

    针对具有空索引的表运行不同版本的 proc：

    CREATE OR ALTER PROCEDURE dbo.LargeUnusedGrant_alt1 (@OwnerUserId INT)
AS 
BEGIN

    SELECT TOP 200 *
    FROM dbo.Posts AS p
    WHERE p.OwnerUserId = @OwnerUserId
    AND p.PostTypeId = 1
    ORDER BY p.Score DESC, p.Id DESC;

END;
GO

    使用相同的值：

    EXEC dbo.LargeUnusedGrant_alt1 @OwnerUserId = 8672;
GO

    第一次运行：悲伤的记忆授予

    第二轮：快乐记忆补助金

    多余的左连接：

    /*Niko's Triko*/
CREATE OR ALTER PROCEDURE dbo.LargeUnusedGrant_alt2 (@OwnerUserId INT)
AS 
BEGIN

    CREATE TABLE #t1 (Id INT, INDEX cx_whatever CLUSTERED COLUMNSTORE);

    SELECT TOP 200 *
    FROM dbo.Posts AS p
    LEFT JOIN #t1 ON 1 = 1
    WHERE p.OwnerUserId = @OwnerUserId
    AND p.PostTypeId = 1
    ORDER BY p.Score DESC, p.Id DESC;

END;
GO 

EXEC dbo.LargeUnusedGrant_alt2 @OwnerUserId = 8672;
GO

    第一次运行：悲伤的记忆授予

    第二轮：快乐记忆补助金

    • 5

相关问题