sql-server - "There is insufficient system memory in resource pool ' 执行存储过程时默认' to run this query"

Question

我正在使用循环遍历游标的存储过程。在游标内，我正在调用一个 native 编译的存储过程。 native 编译的存储过程会插入到内存优化表中。

我的问题是一段时间后(大约 3 分钟)我遇到错误“资源池‘默认’中的系统内存不足，无法运行此查询”。

我追踪到了问题，似乎是插入语句(或其底层查询)造成了问题并增加了内存，这似乎在插入之后没有被释放，也没有在存储过程之后被释放。

我从大约 3 GB 的已用内存开始(在我的数据库上)，当查询运行时它逐步增加到 12 GB(这是限制)并导致错误。报错后内存立马下降到3GB，这说明不可能是inserting table size本身的问题。在我的主存储过程中，它大约有 29 个循环(在游标中)，因此游标本身工作正常。如果我删除插入语句(请参见下面的代码)，一切都会很好。所以问题一定是插入语句(resp。它是基础查询)。我不明白，为什么 SQL Server 在插入后似乎没有释放内存(或者至少在执行 native 存储过程之后)。

关于如何解决该问题的任何想法(我使用的是 SQL Server 2014)？

这里是 native 编译存储过程的代码:

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

确保可用于 SQL Server 的最大内存已设置上限，以便操作系统仍有可用内存。我通常为 OS 分配 2 GB。例如如果总可用 RAM 为 8 GB，则 SQL Server 可用的总内存上限为 6 GB。