4

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:

  1. Turning off query store
  2. Updating SQL Server thru CU27
  3. Tracing the query (no good results)
  4. Running as many reports as we can find to pinpoint the issue

Recent changes that correlate to the timing of the error:

  1. Turning on Query Store
  2. Addition of a large number of indexes

Other information:

  1. We have 32gb of memory on the server and allocate 26gb to sql server
  2. The TARGET table of the MERGE is a CLUSTERED COLUMNSTORE INDEX
  3. The SOURCE table of the MERGE is a HEAP
  4. 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:

  1. Update SQL version
  2. Modify your query
  3. 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 - 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;
2
  • 2
    It (probably) doesn't solve the problem, but I do suggest reading So, you want to use MERGE, eh?: TL;DR: "Please don't, but if you insist: Please ALWAYS use HOLDLOCK on the target."
    – Thom A
    Commented Dec 15, 2021 at 18:33
  • No memory optimized anything or resource governor or SQLCLR. I will look into the "Lock pages in memory" right on the system for the service account. @Charlieface
    – Antyan
    Commented Dec 16, 2021 at 14:20

2 Answers 2

6

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.

1
  • There are a significant number of problems already documented with MERGE listed here.
    – Zane
    Commented Dec 20, 2021 at 16:33
2

"There is insufficient system memory in resource pool 'internal' to run this query." Check the spid associated with the error message in the error log. Because the target of the merge is a clustered columnstore index, I strongly suspect the error is not directly associated with the the MERGE, rather a consequence of the MERGE. A message from the MERGE itself shouldn't be in the internal resource pool.

The background tuple mover does run in the internal resource pool. Unless any remaining delta stores are compressed with a reorg with (COMPRESS_ALL_ROW_GROUPS = ON), at some point the tuple mover will close and compress delta stores. It needs a memory grant to do so. If the tuple mover times out for its memory grant or the minimal grant for tuple mover isn't available, that error is the outcome. I believe the tuple mover always uses a 1 GB memory grant.

6
  • 1
    Thank you for your reply. I looked into the error log and found the associated SPID. I am not sure what to do with it now that I have it, I am still learning a lot of this stuff. I read up on the tuple mover. I would be surprised if the tuple mover which uses 1GB whenever delta stores hit a certain size threshold would be the culprit here unless multiple delta stores hit the threshold simultaneously and the tuple mover required a new memory grant for each? But my understanding is it would only need the 1 gb grant once. I am not sure what to do next from your answer.
    – Antyan
    Commented Dec 16, 2021 at 14:27
  • Does the spid have an 's' at the end - like 42s - or is it under 50? if so, it is a system thread. You're right about the 1 gb grant making it seem unlikely. Even if multiple delta stores quallify for tuple mover at the same time, only 1 system thread at a time will run tuple mover.
    – sqL_handLe
    Commented Dec 17, 2021 at 13:16
  • So other than tuple mover, another good candidate would be a SQL Server 'Task Manager' session running an async auto stats update, if auto stats update and auto stats update async are both enabled in the database.
    – sqL_handLe
    Commented Dec 17, 2021 at 13:18
  • Thank you for your time looking into this. A comment on the original question has solved the issue for me. Unfortunately I still do not know what the root cause was but by switching from a MERGE to separate INSERT/UPDATE/DELETE statements the memory issue has gone away. I appreciate that you are attempting to determine the root cause though as it may be more helpful if someone runs into this problem in the future. The spid was 61, no s and over 50.
    – Antyan
    Commented Dec 20, 2021 at 15:17
  • glad you were able to resolve it!! i never did ask - were the errors returned to the client that requested the query, or discovered in the error log without being retrned to the client?
    – sqL_handLe
    Commented Dec 20, 2021 at 22:19

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.