主页 / dba / 问题 / 220938
Accepted
user9516827
Asked: 2018-10-25 12:15:23 +0800 CST

巨大的内存授予导致 RESOURCE SEMAPHORE 等待其他查询

  • 772

问题：

我几乎没有请求大量内存授予（~7GB）的查询。这些查询经常运行，这导致其他查询等待内存。所以我看到 RESOURCE_SEMAPHORE 等待类型。服务器信息：

  1. Microsoft SQL Server 2016 (SP2) (KB4052908) - Windows Server 2016 Standard 10.0（内部版本 14393：）上的 13.0.5026.0 (X64)
  2. 128 GB 内存
  3. 96 GB 作为最大服务器内存
  4. 数据库大小为 1.6 TB
  5. 启用事务复制
  6. 查询存储已禁用

我知道我已经发布了关于此服务器性能的类似问题。我试图从不同的角度看待事物或尝试以不同的方式解决问题。正如这篇文章中提到的，我有某种记忆压力。现在我正在尝试修复查询中的问题或摆脱这个巨大的内存授予。

这是帖子中提到的查询的计划。

执行计划

我了解到这SORT,HASH JOIN,EXCHANGE(parallel Distribute Stream and Parallel Re partition stream and Parallel Gather Stream)是消耗内存的迭代器，我在我的执行计划中看到了这些。

我怎样才能从这个查询中减少这个巨大的内存授予。？即使我很困惑内存压力是否是由于一堆查询授予的巨大内存造成的。？

这是我使用 SQLServer 探查器捕获查询时的实际查询。

exec sp_executesql N'SELECT TOP (@p__linq__6) 
    [PJ2].[FormId] AS [FormId], 
    [PJ2].[TNUMId] AS [TNUMId], 
    [PJ2].[TNUMDateTime] AS [TNUMDateTime], 
    [PJ2].[TNUMEventNumber] AS [TNUMEventNumber], 
    [PJ2].[EventNumber] AS [EventNumber], 
    [PJ2].[SubUnit] AS [SubUnit], 
    [PJ2].[EventClass] AS [EventClass], 
    [PJ2].[NatureOfEvent] AS [NatureOfEvent], 
    [PJ2].[EventType] AS [EventType], 
    [PJ2].[FileNumber] AS [FileNumber], 
    [PJ2].[EventStatus] AS [EventStatus], 
    [PJ2].[TNUMDate] AS [TNUMDate], 
    [PJ2].[FileDate] AS [FileDate], 
    [PJ2].[ComplainantFirstName] AS [ComplainantFirstName], 
    [PJ2].[ComplainantLastName] AS [ComplainantLastName], 
    [PJ2].[InvestBy] AS [InvestBy], 
    [PJ2].[SecondaryManager] AS [SecondaryManager], 
    [PJ2].[RejectionReason] AS [RejectionReason], 
    [PJ2].[InactiveReason] AS [InactiveReason], 
    [PJ2].[InactiveDate] AS [InactiveDate], 
    [PJ2].[Ag_Id] AS [Ag_Id], 
    [PJ2].[Queue] AS [Queue], 
    [PJ2].[SL] AS [SL], 
    [PJ2].[PrimaryManagerId] AS [PrimaryManagerId], 
    [PJ2].[WarehouseDistrictId] AS [WarehouseDistrictId], 
    [PJ2].[WarehouseIsSQ] AS [WarehouseIsSQ], 
    [PJ2].[WarehousePhone] AS [WarehousePhone], 
    [PJ2].[CityTwp] AS [CityTwp], 
    [PJ2].[County] AS [County], 
    [PJ2].[Institution] AS [Institution], 
    [PJ2].[TNUMTime] AS [TNUMTime], 
    [PJ2].[Prefix] AS [Prefix], 
    [PJ2].[StreetNumber] AS [StreetNumber], 
    [PJ2].[Street] AS [Street], 
    [PJ2].[RoadType] AS [RoadType], 
    [PJ2].[Suffix] AS [Suffix], 
    [PJ2].[Apartment] AS [Apartment], 
    [PJ2].[AtOrNear] AS [AtOrNear], 
    [PJ2].[State] AS [State], 
    [PJ2].[Zip] AS [Zip], 
    [PJ2].[Beat] AS [Beat], 
    [PJ2].[Reviewed] AS [Reviewed], 
    [PJ2].[WarehouseCounty] AS [WarehouseCounty], 
    [PJ2].[IsTrue] AS [IsTrue], 
    [PJ2].[EnquiredByUserId] AS [EnquiredByUserId], 
    [PJ2].[SecondaryManagerUserId] AS [SecondaryManagerUserId], 
    [PJ2].[DiaryNumber] AS [DiaryNumber], 
    [PJ2].[CI_Value] AS [CI_Value], 
    [PJ2].[CurrentEventStatus_Description] AS [CurrentEventStatus_Description]
    FROM ( SELECT [PJ2].[FormId] AS [FormId], [PJ2].[TNUMId] AS [TNUMId], [PJ2].[TNUMDateTime] AS [TNUMDateTime], [PJ2].[TNUMEventNumber] AS 
[TNUMEventNumber], [PJ2].[EventNumber] AS [EventNumber], [PJ2].[IsTrue] AS [IsTrue], [PJ2].[SubUnit] AS [SubUnit], [PJ2].[EventClass] AS [EventClass], 
[PJ2].[NatureOfEvent] AS [NatureOfEvent], [PJ2].[EventType] AS [EventType], [PJ2].[FileNumber] AS [FileNumber], [PJ2].[EventStatus] AS [EventStatus], [PJ2].[TNUMDate] AS [TNUMDate], [PJ2].[FileDate] AS [FileDate], [PJ2].[ComplainantFirstName] AS [ComplainantFirstName], [PJ2].[ComplainantLastName] AS [ComplainantLastName], [PJ2].[InvestBy] AS [InvestBy], [PJ2].[SecondaryManager] AS [SecondaryManager], [PJ2].[EnquiredByUserId] AS [EnquiredByUserId], [PJ2].[SecondaryManagerUserId] AS [SecondaryManagerUserId], [PJ2].[RejectionReason] AS [RejectionReason], [PJ2].[InactiveReason] AS [InactiveReason], [PJ2].[InactiveDate] AS [InactiveDate], [PJ2].[Ag_Id] AS [Ag_Id], [PJ2].[Queue] AS [Queue], [PJ2].[SL] AS [SL], [PJ2].[PrimaryManagerId] AS [PrimaryManagerId], [PJ2].[WarehouseDistrictId] AS [WarehouseDistrictId], [PJ2].[WarehouseIsSQ] AS [WarehouseIsSQ], [PJ2].[WarehousePhone] AS [WarehousePhone], [PJ2].[CityTwp] AS [CityTwp], [PJ2].[County] AS [County], [PJ2].[Institution] AS [Institution], [PJ2].[TNUMTime] AS [TNUMTime], [PJ2].[Prefix] AS [Prefix], [PJ2].[StreetNumber] AS [StreetNumber], [PJ2].[Street] AS [Street], [PJ2].[RoadType] AS [RoadType], [PJ2].[Suffix] AS [Suffix], [PJ2].[Apartment] AS [Apartment], [PJ2].[AtOrNear] AS [AtOrNear], [PJ2].[State] AS [State], [PJ2].[Zip] AS [Zip], [PJ2].[Beat] AS [Beat], [PJ2].[Reviewed] AS [Reviewed], [PJ2].[CI_Value] AS [CI_Value], [PJ2].[CurrentEventStatus_Description] AS [CurrentEventStatus_Description], [PJ2].[DiaryNumber] AS [DiaryNumber], [PJ2].[WarehouseCounty] AS [WarehouseCounty], row_number() OVER (ORDER BY [PJ2].[Ag_Id] ASC, [PJ2].[TNUMEventNumber] ASC, [PJ2].[FileNumber] ASC) AS [row_number]
        FROM ( SELECT 
            [Ex1].[FormId] AS [FormId], 
            [Ex1].[TNUMId] AS [TNUMId], 
            [Ex1].[TNUMDateTime] AS [TNUMDateTime], 
            [Ex1].[TNUMEventNumber] AS [TNUMEventNumber], 
            [Ex1].[EventNumber] AS [EventNumber], 
            [Ex1].[IsTrue] AS [IsTrue], 
            [Ex1].[SubUnit] AS [SubUnit], 
            [Ex1].[EventClass] AS [EventClass], 
            [Ex1].[NatureOfEvent] AS [NatureOfEvent], 
            [Ex1].[EventType] AS [EventType], 
            [Ex1].[FileNumber] AS [FileNumber], 
            [Ex1].[EventStatus] AS [EventStatus], 
            [Ex1].[TNUMDate] AS [TNUMDate], 
            [Ex1].[FileDate] AS [FileDate], 
            [Ex1].[ComplainantFirstName] AS [ComplainantFirstName], 
            [Ex1].[ComplainantLastName] AS [ComplainantLastName], 
            [Ex1].[InvestBy] AS [InvestBy], 
            [Ex1].[SecondaryManager] AS [SecondaryManager], 
            [Ex1].[EnquiredByUserId] AS [EnquiredByUserId], 
            [Ex1].[SecondaryManagerUserId] AS [SecondaryManagerUserId], 
            [Ex1].[RejectionReason] AS [RejectionReason], 
            [Ex1].[InactiveReason] AS [InactiveReason], 
            [Ex1].[InactiveDate] AS [InactiveDate], 
            [Ex1].[Ag_Id] AS [Ag_Id], 
            [Ex1].[Queue] AS [Queue], 
            [Ex1].[SL] AS [SL], 
            [Ex1].[PrimaryManagerId] AS [PrimaryManagerId], 
            [Ex1].[WarehouseDistrictId] AS [WarehouseDistrictId], 
            [Ex1].[WarehouseIsSQ] AS [WarehouseIsSQ], 
            [Ex1].[WarehousePhone] AS [WarehousePhone], 
            [Ex1].[CityTwp] AS [CityTwp], 
            [Ex1].[County] AS [County], 
            [Ex1].[Institution] AS [Institution], 
            [Ex1].[TNUMTime] AS [TNUMTime], 
            [Ex1].[Prefix] AS [Prefix], 
            [Ex1].[StreetNumber] AS [StreetNumber], 
            [Ex1].[Street] AS [Street], 
            [Ex1].[RoadType] AS [RoadType], 
            [Ex1].[Suffix] AS [Suffix], 
            [Ex1].[Apartment] AS [Apartment], 
            [Ex1].[AtOrNear] AS [AtOrNear], 
            [Ex1].[State] AS [State], 
            [Ex1].[Zip] AS [Zip], 
            [Ex1].[Beat] AS [Beat], 
            [Ex1].[Reviewed] AS [Reviewed], 
            [Ex1].[CI_Value] AS [CI_Value], 
            [Ex1].[CurrentEventStatus_Description] AS [CurrentEventStatus_Description], 
            [Ex1].[DiaryNumber] AS [DiaryNumber], 
            [Ex1].[WarehouseCounty] AS [WarehouseCounty]
            FROM (SELECT 
    [SAQE].[FormId] AS [FormId], 
    [SAQE].[TNUMId] AS [TNUMId], 
    [SAQE].[TNUMDateTime] AS [TNUMDateTime], 
    [SAQE].[TNUMEventNumber] AS [TNUMEventNumber], 
    [SAQE].[EventNumber] AS [EventNumber], 
    [SAQE].[IsTrue] AS [IsTrue], 
    [SAQE].[SubUnit] AS [SubUnit], 
    [SAQE].[EventClass] AS [EventClass], 
    [SAQE].[NatureOfEvent] AS [NatureOfEvent], 
    [SAQE].[EventType] AS [EventType], 
    [SAQE].[FileNumber] AS [FileNumber], 
    [SAQE].[EventStatus] AS [EventStatus], 
    [SAQE].[TNUMDate] AS [TNUMDate], 
    [SAQE].[FileDate] AS [FileDate], 
    [SAQE].[ComplainantFirstName] AS [ComplainantFirstName], 
    [SAQE].[ComplainantLastName] AS [ComplainantLastName], 
    [SAQE].[InvestBy] AS [InvestBy], 
    [SAQE].[SecondaryManager] AS [SecondaryManager], 
    [SAQE].[EnquiredByUserId] AS [EnquiredByUserId], 
    [SAQE].[SecondaryManagerUserId] AS [SecondaryManagerUserId], 
    [SAQE].[RejectionReason] AS [RejectionReason], 
    [SAQE].[InactiveReason] AS [InactiveReason], 
    [SAQE].[InactiveDate] AS [InactiveDate], 
    [SAQE].[Ag_Id] AS [Ag_Id], 
    [SAQE].[Queue] AS [Queue], 
    [SAQE].[SL] AS [SL], 
    [SAQE].[PrimaryManagerId] AS [PrimaryManagerId], 
    [SAQE].[WarehouseDistrictId] AS [WarehouseDistrictId], 
    [SAQE].[WarehouseIsSQ] AS [WarehouseIsSQ], 
    [SAQE].[WarehousePhone] AS [WarehousePhone], 
    [SAQE].[CityTwp] AS [CityTwp], 
    [SAQE].[County] AS [County], 
    [SAQE].[Institution] AS [Institution], 
    [SAQE].[TNUMTime] AS [TNUMTime], 
    [SAQE].[Prefix] AS [Prefix], 
    [SAQE].[StreetNumber] AS [StreetNumber], 
    [SAQE].[Street] AS [Street], 
    [SAQE].[RoadType] AS [RoadType], 
    [SAQE].[Suffix] AS [Suffix], 
    [SAQE].[Apartment] AS [Apartment], 
    [SAQE].[AtOrNear] AS [AtOrNear], 
    [SAQE].[State] AS [State], 
    [SAQE].[Zip] AS [Zip], 
    [SAQE].[Beat] AS [Beat], 
    [SAQE].[Reviewed] AS [Reviewed], 
    [SAQE].[CI_Value] AS [CI_Value], 
    [SAQE].[CurrentEventStatus_Description] AS [CurrentEventStatus_Description], 
    [SAQE].[DiaryNumber] AS [DiaryNumber], 
    [SAQE].[WarehouseCounty] AS [WarehouseCounty]
    FROM [dbo].[SAQE] AS [SAQE]) AS [Ex1]
            WHERE ((N''Public'' = [Ex1].[SL]) OR (N''Private'' = [Ex1].[SL]) OR ([Ex1].[PrimaryManagerId] = @p__linq__0) OR ( EXISTS (SELECT 
                1 AS [C1]
                FROM (SELECT 
    [fp].[Id] AS [Id], 
    [fp].[Ag_Id] AS [Ag_Id], 
    [fp].[UserId] AS [UserId]
    FROM [dbo].[fp] AS [fp]) AS [Extent2]
                WHERE ([Ex1].[FormId] = [Extent2].[Id]) AND ([Extent2].[UserId] = @p__linq__1) AND ([Extent2].[Ag_Id] = @p__linq__2)
            ))) AND (([Ex1].[Ag_Id] = @p__linq__3) OR (([Ex1].[Ag_Id] IS NULL) AND (@p__linq__3 IS NULL))) AND (([Ex1].[Queue] = @p__linq__4) OR (([Ex1].[Queue] IS NULL) AND (@p__linq__4 IS NULL)))
        )  AS [PJ2]
    )  AS [PJ2]
    WHERE [PJ2].[row_number] > @p__linq__5
    ORDER BY [PJ2].[Ag_Id] ASC, [PJ2].[TNUMEventNumber] ASC, [PJ2].[FileNumber] ASC',N'@p__linq__0 int,@p__linq__1 int,@p__linq__2 varchar(8000),@p__linq__3 varchar(8000),@p__linq__4 varchar(8000),@p__linq__5 int,@p__linq__6 int',@p__linq__0=9495,@p__linq__1=9495,@p__linq__2='3568',@p__linq__3='3568',@p__linq__4='1',@p__linq__5=0,@p__linq__6=100
sql-server performance

1 个回答

  1. Best Answer

    从您发布的执行计划：

    查询在执行期间必须等待 43 秒才能获得 MemoryGrant

    一旦获得内存授权，该查询仅需 51.3 秒即可执行。这意味着该查询总共有 45% 的时间花在了等待内存授予上。如果面临这种极端的等待，我会立即使用Resource Governor或查询提示RESOURCE_SEMAPHORE来限制查询的内存授予，并在稍后整理详细信息。SQL Server 对内存授予相当贪婪，在某些情况下，您可以降低查询的内存授予而不会降低查询性能。MAX_GRANT_PERCENT

    如果您使用的是等效的企业版，则只能实施 Resource Governor 解决方案。REQUEST_MAX_MEMORY_GRANT_PERCENT您可以使用工作负载组的选项来限制查询的最大内存授予。如果您想限制许多查询的内存授予，或者如果您无法更改导致问题的查询的查询文本，则资源调控器是一个理想的解决方案。一个不幸的限制是您只能在 SQL Server 2019 之前为该选项指定整数。

    MAX_GRANT_PERCENT如果可以编辑查询文本，则可以在任何版本的 SQL Server 上实施该选项。也可以通过计划指南强制执行此提示，但我从未尝试过。查询提示方法允许您为提示指定小数，因此它比 Resource Governor 更灵活。

    关于你应该限制多少内存授予，我会每次减半，直到RESOURCE_SEMAPHORE等待得到控制。现在，您的授予大约是服务器可用总可授予内存的 10.5% (0.25*7664448/18189472)。如果您想将它减半，那么您可以使用 Resource Governor 将内存授予限制为 5% 或使用MAX_GRANT_PERCENT hint. 百分比之所以不同，是因为MAX_GRANT_PERCENT提示会影响查询可用的最大内存授予，而资源调控器设置决定了查询可用的最大内存授予。

    确实，过多地限制内存授予会导致溢出到 tempdb，这可能对性能不利。根据 IO 性能，该溢出花费的时间可能比您当前等待内存授予所花费的时间少得多。使用最大可用内存百分比的运算符是节点 41 处的散列连接：

    散列连接

    基于此，我最保守的猜测是，您可以将查询内存授予从 7664448 KB 一直降低到 4350000 KB，并且仍然可以避免 tempdb 溢出。准确的数字只能通过测试来确定。

    如果您需要其他选项，您可以考虑更改索引或进行其他调整以获得具有更少散列连接或排序的查询计划。您的基数估计对于这些运算符非常好，并且与计划的其余部分相比，这些运算符处理相对大量的行，因此这不是我的第一种方法。

    • 10

相关问题