SQL Server 2017; Solução de problemas de memória; Não há memória de sistema suficiente no pool de recursos 'interno' para executar esta consulta

Estamos executando um SQL Server 2017 local para dar suporte a um banco de dados datawarehouse. O banco de dados é carregado em um agendamento por meio do SSIS em grande parte pelo uso de tabelas de preparo e da função MERGE. Recentemente, começamos a ver o erro "Há memória de sistema insuficiente no pool de recursos 'interno' para executar esta consulta". Ele se tornou cada vez mais desenfreado nas últimas semanas.

Nós tentamos:

1. Desativando o armazenamento de consultas
2. Atualizando o SQL Server através do CU27
3. Rastreando a consulta (sem bons resultados)
4. Executando o máximo de relatórios que pudermos encontrar para identificar o problema

Alterações recentes que se correlacionam com o tempo do erro:

1. Ativando o repositório de consultas
2. Adição de um grande número de índices

Outra informação:

1. Temos 32gb de memória no servidor e alocamos 26gb para o sql server
2. A tabela TARGET do MERGE é um CLUSTERED COLUMNSTORE INDEX
3. A tabela SOURCE do MERGE é um HEAP
4. Com o tempo/várias falhas, a tabela SOURCE que preparou os registros alterados aumentou para mais de 200.000 registros. A tabela TARGET tem aproximadamente 10 milhões de linhas.

Qualquer ajuda seria apreciada. Eu vasculhei a internet nos últimos dias procurando alguma orientação. Tudo o que vi até agora é:

1. Atualizar versão do SQL
2. Modifique sua consulta
3. Adicionar memória ao servidor

DECLARAÇÃO DE FUSÃO:

 DROP TABLE IF EXISTS #Changes;
DROP TABLE IF EXISTS #TransformedChanges;

CREATE TABLE #Changes
(
    [Change Type] VARCHAR(100)
);
MERGE [dbo].[FactOrderLine] AS TARGET
USING ( SELECT
[FactOrderLine].[OrderLine_Key],
[FactOrderLine].[BookedDate_Date_Key],
[FactOrderLine].[BookedDate_Time_Key],
[FactOrderLine].[Account_Key],
[FactOrderLine].[CCN_Key],
[FactOrderLine].[BillTo_SalesOffice_Key],
[FactOrderLine].[BillTo_Territory_Key],
[FactOrderLine].[ShipTo_SalesOffice_Key],
[FactOrderLine].[ShipTo_Territory_Key],
[FactOrderLine].[AssemblyLocation_Key],
[FactOrderLine].[ProductDivision_Key],
[FactOrderLine].[Product_Key],
[FactOrderLine].[Booked Date],
[FactOrderLine].[Ordered Quantity],
[FactOrderLine].[Unit Price - CCN],
[FactOrderLine].[Unit Price - Transaction],
[FactOrderLine].[Discount Factor],
[FactOrderLine].[Split Factor],
[FactOrderLine].[Is Split?],
[DW_Checksum] = CHECKSUM([FactOrderLine].[BookedDate_Date_Key],
[FactOrderLine].[BookedDate_Time_Key],
[FactOrderLine].[Account_Key],
[FactOrderLine].[CCN_Key],
[FactOrderLine].[BillTo_SalesOffice_Key],
[FactOrderLine].[BillTo_Territory_Key],
[FactOrderLine].[ShipTo_SalesOffice_Key],
[FactOrderLine].[ShipTo_Territory_Key],
[FactOrderLine].[AssemblyLocation_Key],
[FactOrderLine].[ProductDivision_Key],
[FactOrderLine].[Product_Key],
[FactOrderLine].[Booked Date],
[FactOrderLine].[Ordered Quantity],
[FactOrderLine].[Unit Price - CCN],
[FactOrderLine].[Unit Price - Transaction],
[FactOrderLine].[Discount Factor],
[FactOrderLine].[Split Factor],
[FactOrderLine].[Is Split?],
0)
FROM [changeLog].[FactOrderLine] ) AS SOURCE
ON [Source].[OrderLine_Key] = [Target].[OrderLine_Key]
WHEN MATCHED AND ISNULL([Source].[DW_Checksum], 0) <> ISNULL([Target].[DW_Checksum], 0) THEN UPDATE SET
[Target].[BookedDate_Date_Key] = [Source].[BookedDate_Date_Key],
[Target].[BookedDate_Time_Key] = [Source].[BookedDate_Time_Key],
[Target].[Account_Key] = [Source].[Account_Key],
[Target].[CCN_Key] = [Source].[CCN_Key],
[Target].[BillTo_SalesOffice_Key] = [Source].[BillTo_SalesOffice_Key],
[Target].[BillTo_Territory_Key] = [Source].[BillTo_Territory_Key],
[Target].[ShipTo_SalesOffice_Key] = [Source].[ShipTo_SalesOffice_Key],
[Target].[ShipTo_Territory_Key] = [Source].[ShipTo_Territory_Key],
[Target].[AssemblyLocation_Key] = [Source].[AssemblyLocation_Key],
[Target].[ProductDivision_Key] = [Source].[ProductDivision_Key],
[Target].[Product_Key] = [Source].[Product_Key],
[Target].[Booked Date] = [Source].[Booked Date],
[Target].[Ordered Quantity] = [Source].[Ordered Quantity],
[Target].[Unit Price - CCN] = [Source].[Unit Price - CCN],
[Target].[Unit Price - Transaction] = [Source].[Unit Price - Transaction],
[Target].[Discount Factor] = [Source].[Discount Factor],
[Target].[Split Factor] = [Source].[Split Factor],
[Target].[Is Split?] = [Source].[Is Split?],
[Target].[DW_Checksum] = [Source].[DW_Checksum],
[Target].[DW_ModifiedOn] = GETUTCDATE(),
[Target].[DW_IsDeleted?] = 0
WHEN NOT MATCHED BY TARGET THEN INSERT
(
[OrderLine_Key],
[BookedDate_Date_Key],
[BookedDate_Time_Key],
[Account_Key],
[CCN_Key],
[BillTo_SalesOffice_Key],
[BillTo_Territory_Key],
[ShipTo_SalesOffice_Key],
[ShipTo_Territory_Key],
[AssemblyLocation_Key],
[ProductDivision_Key],
[Product_Key],
[Booked Date],
[Ordered Quantity],
[Unit Price - CCN],
[Unit Price - Transaction],
[Discount Factor],
[Split Factor],
[Is Split?],
[DW_IsDeleted?], [DW_Checksum], [Source_ModifiedOn], [DW_ModifiedOn], [DW_CreatedOn] ) VALUES (
[Source].[OrderLine_Key],
[Source].[BookedDate_Date_Key],
[Source].[BookedDate_Time_Key],
[Source].[Account_Key],
[Source].[CCN_Key],
[Source].[BillTo_SalesOffice_Key],
[Source].[BillTo_Territory_Key],
[Source].[ShipTo_SalesOffice_Key],
[Source].[ShipTo_Territory_Key],
[Source].[AssemblyLocation_Key],
[Source].[ProductDivision_Key],
[Source].[Product_Key],
[Source].[Booked Date],
[Source].[Ordered Quantity],
[Source].[Unit Price - CCN],
[Source].[Unit Price - Transaction],
[Source].[Discount Factor],
[Source].[Split Factor],
[Source].[Is Split?],
0,
[Source].[DW_Checksum],NULL,GETUTCDATE(),GETUTCDATE()
)
OUTPUT $action INTO #Changes;
CREATE TABLE #TransformedChanges
    (
        [Update Record Count] INT,
        [Insert Record Count] INT
    );
INSERT INTO #TransformedChanges
SELECT *
FROM
    (
        SELECT 
            TRIM(#Changes.[Change Type])+' Record Count' AS [Change Type],
            COUNT(*) AS [Record Count]
        FROM #Changes
        GROUP BY TRIM(#Changes.[Change Type])+' Record Count'
    ) A
PIVOT
    (
        SUM([Record Count])
        FOR A.[Change Type] IN ([Update Record Count], [Insert Record Count])
    ) [B];
DECLARE @TransformationChecker INT;
SET @TransformationChecker = (SELECT COUNT(*) FROM #TransformedChanges);
IF @TransformationChecker = 0 INSERT INTO #TransformedChanges VALUES(0,0);
SELECT ISNULL([Update Record Count], 0) [Update Record Count], ISNULL([Insert Record Count], 0) [Insert Record Count] FROM #TransformedChanges;

DROP TABLE IF EXISTS #Changes;
DROP TABLE IF EXISTS #TransformedChanges;