"Недостаточно системной памяти в пуле ресурсов" по умолчанию "для выполнения этого запроса" во время выполнения хранимой процедуры

Question

Я использую хранимую процедуру, которая проходит через курсор. Внутри курсора я вызываю встроенную хранимую процедуру. Собственно скомпилированная хранимая процедура выполняет вставку в оптимизированную для памяти таблицу.

Моя проблема заключается в том, что я через некоторое время сталкиваюсь с ошибкой "Недостаточно системной памяти в пуле ресурсов" по умолчанию "для выполнения этого запроса" (около 3 минут).

Я разыскал проблему, и кажется, что оператор вставки (или его базовый запрос) создает проблему и накачивает память, которая, кажется, не освобождается после вставки, а также после хранимой процедуры.

Я начинаю с примерно 3 ГБ используемой памяти (в моей базе данных), в то время как запрос выполняется, он идет шаг за шагом до 12 ГБ (что является пределом) и вызывает ошибку. После ошибки объем памяти сразу уменьшается до 3 ГБ, что говорит о том, что проблема не может быть связана с самим размером таблицы. В моей основной хранимой процедуре это около 29 циклов (в курсоре), поэтому сам курсор работает нормально. Если я удаляю оператор вставки (см. Код ниже), все работает отлично. Таким образом, проблема должна заключаться в операторе вставки (соответственно, это базовый запрос). Я не понимаю, почему сервер SQL, кажется, не освобождает память после вставки (или, по крайней мере, после того, как собственная хранимая процедура была выполнена).

Любые идеи, как решить эту проблему (я использую SQL Server 2014)?

Вот код изначально скомпилированной хранимой процедуры:

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, ограничен таким образом, чтобы для ОС оставалась доступной память. Я обычно выделяю 2 ГБ для ОС. Например, если общий объем доступной оперативной памяти составляет 8 ГБ, ограничьте общий объем доступной памяти для SQL Server 6 ГБ.

Answer 2

Проблема, похоже, связана с нехваткой памяти. Я решаю эту проблему не как администратор базы данных, а как разработчик. Нужно "передавать" или буферизовать результаты, собранные запросом, и иметь возможность отказаться от того, что было прочитано до сих пор, прежде чем читать больше данных и потреблять больше физической памяти. Так, например, прочтите 1 ГБ, сделайте то, что необходимо сделать с этими строками данных, перезапишите эту память, уже выделенную для объекта / буфера / чего-либо еще, на 1 ГБ, прочитав больше строк, а затем снова обработайте эти строки и так далее. Я не хотел бы пытаться делать это в хранимой процедуре.