"There is insufficient system memory in resource pool 'default' to run this query" during execution of stored procedure

Question

I am using a stored procedure which loops through a cursor. Within the cursor I am calling a natively compiled stored procedure. The natively compiled stored procedure does an insert into a memory-optimized table.

My problem is that I run into the error "There is insufficient system memory in resource pool 'default' to run this query" after a while (3 minutes or so).

I tracked down the problem and it seems that the insert statement (or it's underlying query) creates the problem and pumps up the memory which seems not being released after the insert and also not after the stored procedure.

I start with around 3 GB used memory (on my database), while the query is running it goes step by step up to 12 GB (which is the limit) and causes the error. After the error, the memory goes down to 3 GB immediately, which shows me it can not be the problem of the inserting table size itself. In my main stored procedure it is about 29 loops (in the cursor) so the cursor itself works fine. If I remove the insert statement (see code below) everything works perfectly fine. So the problem must be the insert statement (resp. it's underlying query). I do not understand, why SQL server seems not releasing the memory after the insert (or at least after the native stored procedure has been executed).

Any ideas how to solve that problem (I am using SQL Server 2014)?

Here the code of the natively compiled stored procedure:

create procedure [CombinedStrategies].[spInsParameterCombinationNative]
(   
    @UniqueProcessingBlockID int,
    @MS2ObjectID54RestricationParameterGroupID int,
    @MS11ObjectID54RestricationParameterGroupID int,
    @MS15SBBObjectID54RestricationParameterGroupID int,
    @MS15SBBObjectID59RestricationParameterGroupID int,
    @MS15SBBObjectID62RestricationParameterGroupID int,
    @MS15SFObjectID54RestricationParameterGroupID int,
    @MS15SFObjectID59RestricationParameterGroupID int,
    @MS15SBObjectID54RestricationParameterGroupID int,
    @MS15SBObjectID59RestricationParameterGroupID int,
    @MS15SBObjectID62RestricationParameterGroupID int,
    @MS16ObjectID54RestricationParameterGroupID int,
    @MS16ObjectID62RestricationParameterGroupID int,
    @CombinedParametersMS2 CombinedStrategies.ParameterGroupIDs readonly,
    @CombinedParametersMS11 CombinedStrategies.ParameterGroupIDs readonly,
    @CombinedParametersMS16ObjectID54 CombinedStrategies.ParameterGroupIDs readonly,
    @CombinedParametersMS16ObjectID62 CombinedStrategies.ParameterGroupIDs readonly,
    @CombinedParametersMS15SBObjectID54 CombinedStrategies.ParameterGroupIDs readonly,
    @CombinedParametersMS15SBObjectID59 CombinedStrategies.ParameterGroupIDs readonly,
    @CombinedParametersMS15SBObjectID62 CombinedStrategies.ParameterGroupIDs readonly,
    @CombinedParametersMS15SBBObjectID54 CombinedStrategies.ParameterGroupIDs readonly,
    @CombinedParametersMS15SBBObjectID59 CombinedStrategies.ParameterGroupIDs readonly,
    @CombinedParametersMS15SBBObjectID62 CombinedStrategies.ParameterGroupIDs readonly,
    @CombinedParametersMS15SFObjectID54 CombinedStrategies.ParameterGroupIDs readonly,
    @CombinedParametersMS15SFObjectID59 CombinedStrategies.ParameterGroupIDs readonly
)
with native_compilation, schemabinding, execute as owner
as 
begin atomic
with (transaction isolation level=snapshot, language=N'us_english')


    -- load parameter combinations into table

    insert into CombinedStrategies.ParameterCombinationForCursorTemp
    (
        UniqueProcessingBlockID,
        MS2ObjectID54ParameterGroupID,
        MS11ObjectID54ParameterGroupID,
        MS15SBBObjectID54ParameterGroupID,
        MS15SBBObjectID59ParameterGroupID,
        MS15SBBObjectID62ParameterGroupID,
        MS15SFObjectID54ParameterGroupID,
        MS15SFObjectID59ParameterGroupID,
        MS15SBObjectID54ParameterGroupID,
        MS15SBObjectID59ParameterGroupID,
        MS15SBObjectID62ParameterGroupID,
        MS16ObjectID54ParameterGroupID,
        MS16ObjectID62ParameterGroupID
    )
    select @UniqueProcessingBlockID,
    MS2_54.ParameterGroupID,
    MS11_54.ParameterGroupID,
    MS15_SSB_54.ParameterGroupID,
    MS15_SSB_59.ParameterGroupID,
    MS15_SSB_62.ParameterGroupID,
    MS15_SF_54.ParameterGroupID,
    MS15_SF_59.ParameterGroupID,
    MS15_SB_54.ParameterGroupID,
    MS15_SB_59.ParameterGroupID,
    MS15_SB_62.ParameterGroupID,
    MS16_54.ParameterGroupID,
    MS16_62.ParameterGroupID
    from @CombinedParametersMS2 as MS2_54,
    @CombinedParametersMS11 as MS11_54,
    @CombinedParametersMS15SBBObjectID59 as MS15_SSB_54,
    @CombinedParametersMS15SBBObjectID59 as MS15_SSB_59,
    @CombinedParametersMS15SBBObjectID62 as MS15_SSB_62,
    @CombinedParametersMS15SFObjectID54 as MS15_SF_54,
    @CombinedParametersMS15SFObjectID59 as MS15_SF_59,
    @CombinedParametersMS15SBObjectID54 as MS15_SB_54,
    @CombinedParametersMS15SBObjectID59 as MS15_SB_59,
    @CombinedParametersMS15SBObjectID62 as MS15_SB_62,
    @CombinedParametersMS16ObjectID54 as MS16_54,
    @CombinedParametersMS16ObjectID62 as MS16_62
    where MS2_54.ParameterGroupID = isnull(@MS2ObjectID54RestricationParameterGroupID, MS2_54.ParameterGroupID)
    and MS11_54.ParameterGroupID = isnull(@MS11ObjectID54RestricationParameterGroupID, MS11_54.ParameterGroupID)
    and MS15_SSB_54.ParameterGroupID = isnull(@MS15SBBObjectID54RestricationParameterGroupID, MS15_SSB_54.ParameterGroupID)
    and MS15_SSB_59.ParameterGroupID = isnull(@MS15SBBObjectID59RestricationParameterGroupID, MS15_SSB_59.ParameterGroupID)
    and MS15_SSB_62.ParameterGroupID = isnull(@MS15SBBObjectID62RestricationParameterGroupID, MS15_SSB_62.ParameterGroupID)
    and MS15_SF_54.ParameterGroupID = isnull(@MS15SFObjectID54RestricationParameterGroupID, MS15_SF_54.ParameterGroupID)
    and MS15_SF_59.ParameterGroupID = isnull(@MS15SFObjectID59RestricationParameterGroupID, MS15_SF_59.ParameterGroupID)
    and MS15_SB_54.ParameterGroupID = isnull(@MS15SBObjectID54RestricationParameterGroupID, MS15_SB_54.ParameterGroupID)
    and MS15_SB_59.ParameterGroupID = isnull(@MS15SBObjectID59RestricationParameterGroupID, MS15_SB_59.ParameterGroupID)
    and MS15_SB_62.ParameterGroupID = isnull(@MS15SBObjectID62RestricationParameterGroupID, MS15_SB_62.ParameterGroupID)
    and MS16_54.ParameterGroupID = isnull(@MS16ObjectID54RestricationParameterGroupID, MS16_54.ParameterGroupID)
    and MS16_62.ParameterGroupID = isnull(@MS16ObjectID62RestricationParameterGroupID, MS16_62.ParameterGroupID)


end 

Answer 1

Make sure the max memory available for SQL Server is capped such that there is still memory available for OS . I usually allocate 2 GB for OS . For e.g. if total available RAM is 8 GB , cap the total memory available to SQL Server is 6 GB.