SQL Server 2017; Memory troubleshooting; There is insufficient system memory in resource pool 'internal' to run this query

We are running a local SQL Server 2017 to support a datawarehouse database. The database is loaded on a schedule through SSIS largely through the use of staging tables and the MERGE function. Recently, we have begun seeing the error "There is insufficient system memory in resource pool 'internal' to run this query.". It has become more and more rampant over the last couple weeks.

We have tried:

• Turning off query store

• Updating SQL Server thru CU27

• Tracing the query (no good results)

• Running as many reports as we can find to pinpoint the issue

Recent changes that correlate to the timing of the error:

• Turning on Query Store

• Addition of a large number of indexes

Other information:

• We have 32gb of memory on the server and allocate 26gb to sql server

• The TARGET table of the MERGE is a CLUSTERED COLUMNSTORE INDEX

• The SOURCE table of the MERGE is a HEAP

• Over time/multiple failures, the SOURCE table that has staged the changed records has grown to over 200,000 records. The TARGET table is ~10 million rows.

Any help would be appreciated. I have scoured the internet for the last couple days looking for any guidance. All I've seen so far is:

• Update SQL version

• Modify your query

• Add memory to the server

MERGE STATEMENT:

```
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

Thanks to the help of multiple commenters I found that my issue was resolved by removing the use of the MERGE statements and instead running independent INSERT/UPDATE/DELETE statements. This was recommended by multiple blogs, a DBA I work with and the commenters here but because of no available documentation that would suggest MERGE and memory issues may be linked (and my own stubborn-ness) I did not attempt switching them until trying multiple other routes.

I am still curious what exactly is going on under the hood that is causing the memory issues when on the surface you would expect the same number of updates, inserts and deletes to require the same (or a similar) amount of resources. Obviously, the MERGE statement does not operate this way and sometimes you have to be practical with solutions.

StackExchange Database Administrators Q#304008, answer score: 6