主页 / dba / 问题 / 319327
Accepted
SEarle1986
Asked: 2022-11-08 15:46:06 +0800 CST

SQL Server 是否可以为查询授予比实例可用的更多内存

  • 772

前几天有人问我，如果 SQL Server 想要运行单个查询，而该查询被授予的内存比实例可用的内存多，会发生什么情况。我最初的想法是我可能会看到RESOURCE_SEMAPHORE等待并且查询永远不会开始。

我做了一些测试试图找出答案。

我的实例在 4000MB RAM 上启动：

EXEC sys.sp_configure N'max server memory (MB)', N'4000'
GO
RECONFIGURE WITH OVERRIDE
GO

如果我然后运行我的（故意可怕的）查询：

USE StackOverflow
SELECT      CONVERT(NVARCHAR(4000), u.DisplayName) AS DisplayName,
            CONVERT(NVARCHAR(MAX), u.DisplayName) AS Disp2,
            CONVERT(NVARCHAR(MAX), u.DisplayName) AS Disp3
FROM        dbo.Users AS u
            JOIN dbo.Posts p
                ON LTRIM(u.DisplayName) = LTRIM(p.Tags)
WHERE       u.CreationDate >= '2008-12-25'
            AND u.CreationDate < '2010-12-26'
ORDER BY    u.CreationDate;

执行计划说授予的内存是 732,008KB。

然后我将我的实例可用的内存设置为低于此数字，然后重新启动实例：

EXEC sys.sp_configure N'max server memory (MB)', N'500' /* a value lower than the previous memory grant */
GO
RECONFIGURE WITH OVERRIDE
GO

我再次运行查询，发现它被授予的内存比以前少（93,176KB），但计划实际上是不同的形状。

然后我再次运行查询并使用查询提示强制原始计划查看授予的内存：

USE StackOverflow
SELECT      CONVERT(NVARCHAR(4000), u.DisplayName) AS DisplayName,
            CONVERT(NVARCHAR(MAX), u.DisplayName) AS Disp2,
            CONVERT(NVARCHAR(MAX), u.DisplayName) AS Disp3
FROM        dbo.Users AS u
            JOIN dbo.Posts p
                ON LTRIM(u.DisplayName) = LTRIM(p.Tags)
WHERE       u.CreationDate >= '2008-12-25'
            AND u.CreationDate < '2010-12-26'
ORDER BY    u.CreationDate
OPTION (RECOMPILE, USE PLAN N'<xml here>'

我发现查询现在使用原始计划，但获得与其编译的计划（93,168KB）非常相似的内存授权 -强制实际计划

这似乎反驳了我的理论，即我会看到RESOURCE_SEMAPHORE等待，并表明存在某种机制可以防止 SQL Server 授予比查询可用的内存更多的内存（这似乎非常明智！）顺便说一下，如果我在同时会话中运行查询两次500MB 服务器设置，然后两个会话都会RESOURCE_SEMAPHORE等待似乎不确定的内容。

我可以MaxQueryMemory在计划中看到，这似乎是阻止 SQL Server 授予比查询可用的更多内存的原因。

是否可以为单个查询授予比实例可用的内存更多的内存？如果不是，是MaxQueryMemory什么原因导致 SQL Server 分配的内存超出了可用内存？这个数字是如何计算的？

注意 - 我的 StackOverflow 数据库处于 130 兼容级别

sql-server

1 个回答

  1. Best Answer

    默认情况下，SQL Server 允许任何查询使用最多 25% 的最大服务器内存来授予内存，但实际上它通常接近 20%。

    资源管理者对此有话要说：

    坚果

    但是在我的最大服务器内存设置为 90GB 的演示 VM 上，我可以获得的最高单查询内存授予是 17GB，而不是 22GB。

    WITH
    c AS 
(
SELECT
    c.*,
    n = 
        ROW_NUMBER() OVER 
        (
            PARTITION BY 
                c.PostId 
            ORDER BY 
                c.Score DESC
        )
FROM dbo.Comments AS c
)
SELECT
    c.*
FROM c
WHERE n = 0;

    这是它的部分计划：

    坚果

    使用sp_PressureDetector，您可以获得有关 SQL Server 内存使用情况的大量信息。为了将其保留在内存授权中，这就是我运行上述查询时发生的情况：

    坚果

    直到我运行了四个查询副本，一个查询才在资源信号量的队列中等待：

    坚果

    等待信息：

    坚果

    考虑它的一般方式是 SQL Server 会将大约 75% 的最大服务器内存分配给请求授权的查询。

    在这种情况下，68GB 的​​内存可用于 90GB 以外的赠款。由于第四个查询将超过约 75% 的标记，因此必须等待。

    一般来说，总授权加上 50% 必须可用于立即授权的内存。如果没有，它会等待，有时直到强制降低授权（查看 中的forced_grant_count列sys.dm_exec_query_resource_semaphores）

    您可以从以下来源更详细地了解 SQL Server 通常如何确定信号量队列和内存授予的优先级：

    请记住，我的回答仅针对传统的行模式和行存储工作负载。添加批处理模式和列存储索引将改变一些行为，特别是在尊重最大服务器内存以及需要内存授权的情况下。

    使用行模式，您将看到授予查询的内存授予的最常见位置是查询计划具有以下功能时：

    • 排序
    • 散列连接或聚合
    • 优化的嵌套循环

    即使查询计划中没有 DML 请求排序来执行压缩，对列存储索引的插入也会要求内存。

    • 16

相关问题