Huge Memory Grant resulting in RESOURCE SEMAPHORE waits for other queries

Question

Issue:

I have few queries which is requesting huge memory grants(~7GB).These queries are run often and this is causing other queries to wait for memory.So i am seeing RESOURCE_SEMAPHORE wait type. Server info:

1. Microsoft SQL Server 2016 (SP2) (KB4052908) - 13.0.5026.0 (X64) on Windows Server 2016 Standard 10.0 (Build 14393: )
2. 128 GB RAM
3. 96 GB as Max Server Memory
4. Database Size is 1.6 TB
5. Have Transaction Replication Enabled
6. Query Store disabled

I know i have posted similar questions on performance of this server.I am trying to see things from different perspective or trying to solve different ways. I have some kind of memory pressure as mentioned in this post. Now i am trying to fix things on the query or get rid this huge memory grant.

Here is the plan for the query mentioned in the post.

Execution Plan

I learned that SORT,HASH JOIN,EXCHANGE(parallel Distribute Stream and Parallel Re partition stream and Parallel Gather Stream) are memory consuming iterators and i see these in my execution plan.

How can i reduce this huge memory grant from this query.? Even i am confused whether the memory pressure is due to huge memory grant by bunch of queries.?

Here is the actual query as i catch the query using SQLServer profiler.

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

Answer 1

From the execution plan that you posted:

The query had to wait 43 seconds for MemoryGrant during execution

The query only took 51.3 s to execute once it got its memory grant. That means that overall 45% of the time for this query was spent waiting for a memory grant. If faced with RESOURCE_SEMAPHORE wait this extreme I would immediately restrict the query's memory grant using Resource Governor or the MAX_GRANT_PERCENT query hint and sort out the details later. SQL Server is fairly greedy with memory grants and in some cases you can lower a query's memory grant without degrading query performance.

You can only implement the Resource Governor solution if you're on an Enterprise Edition equivalent. You can limit the query's maximum memory grant with the REQUEST_MAX_MEMORY_GRANT_PERCENT option for a workload group. Resource Governor is an ideal solution if you want to restrict the memory grants of many queries or if you aren't able to change the query text for the queries causing issues. One unfortunate restriction is that you can only specify integers for that option before SQL Server 2019.

You can implement the MAX_GRANT_PERCENT option on any edition of SQL Server if you can edit the query text. It may also be possible to enforce this hint via a Plan Guide, but I've never tried it. The query hint approach allows you to specify decimals for the hint so it can be more flexible than Resource Governor.

In terms of how much you should restrict the memory grant, I would cut it by half each time until the RESOURCE_SEMAPHORE waits get under control. Right now your grant is about 10.5% (0.25*7664448/18189472) of the total grantable memory available to the server. If you wanted to cut it in half then you would limit the memory grant to 5% using Resource Governor or to 21% using the MAX_GRANT_PERCENT hint. The percentages are different because the MAX_GRANT_PERCENT hint factors in the maximum memory grant available to the query whereas the Resource Governor setting determines the maximum memory grant available to the query.

It is true that restricting the memory grant too much will result in a spill to tempdb, which could be bad for performance. Depending on IO performance, that spill could take significantly less time than you're currently spending waiting for the memory grant. The operator that uses the largest percent of its available memory is the hash join at node 41:

Based on that, my best conservative guess is that you can lower the query memory grant from all the way down from 7664448 KB to 4350000 KB and still avoid a tempdb spill. Precise numbers can only be determined by testing.

If you need another option you can consider changing indexes or making other adjustments to get a query plan with fewer hash joins or sorts. Your cardinality estimates are very good for those operators and those operators process a relatively large number of rows compared to the rest of the plan, so that wouldn't be my first approach.