Why does a simple natively compiled stored procedure run out of memory when table variables are used?

Question

My version of SQL Server is SQL Server 2019 (RTM-CU18). The following repro code requires that an in memory filegroup is created. For anyone following along, please remember that an in-memory filegroup cannot be dropped from a database once it is created.

I have a simple in-memory table in which I insert integers from 1 - 1200:

DROP TABLE IF EXISTS [dbo].[InMem];

CREATE TABLE [dbo].[InMem] (
    i [int] NOT NULL,
    CONSTRAINT [PK_InMem]  PRIMARY KEY NONCLUSTERED (i ASC)
) WITH ( MEMORY_OPTIMIZED = ON , DURABILITY = SCHEMA_ONLY );

INSERT INTO [dbo].[InMem]
SELECT TOP (1200) ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
FROM master..spt_values t1
CROSS JOIN master..spt_values t2;

I also have the following natively compiled stored procedure:

GO

CREATE OR ALTER PROCEDURE p1
WITH NATIVE_COMPILATION, SCHEMABINDING 
AS
BEGIN ATOMIC WITH (TRANSACTION ISOLATION LEVEL = SNAPSHOT, LANGUAGE = N'us_english')
    SELECT c1.i, c2.i, c3.i
    FROM dbo.[InMem] c1
    CROSS JOIN dbo.[InMem] c2
    CROSS JOIN dbo.[InMem] c3
    WHERE c1.i + c2.i + c3.i = 3600;
END;

GO  

The procedure returns one row when executed. On my machine it takes around 32 seconds to complete. I cannot observe any unusual behavior in terms of memory usage while it executes.

I can create a similar table type:

CREATE TYPE [dbo].[InMemType] AS TABLE(
i [int] NOT NULL,
INDEX [ix_WordBitMap] NONCLUSTERED (i ASC)
) WITH ( MEMORY_OPTIMIZED = ON );

as well as the same stored procedure but using the table type instead:

GO

CREATE OR ALTER PROCEDURE p2 (@t dbo.[InMemType] READONLY)
WITH NATIVE_COMPILATION, SCHEMABINDING 
AS
BEGIN ATOMIC WITH (TRANSACTION ISOLATION LEVEL = SNAPSHOT, LANGUAGE = N'us_english')
    SELECT c1.i, c2.i, c3.i
    FROM @t c1
    CROSS JOIN @t c2
    CROSS JOIN @t c3
    WHERE c1.i + c2.i + c3.i = 3600;
END;

GO

The new stored procedure throws an error after about one minute:

Msg 701, Level 17, State 154, Procedure p2, Line 6 [Batch Start Line 57] There is insufficient system memory in resource pool 'default' to run this query.

While the procedure executes I can see the amount of memory used by the MEMORYCLERK_XTP memory clerk increase to around 2800 MB for the database by querying the sys.dm_os_memory_clerks dmv. According to the sys.dm_db_xtp_memory_consumers DMV, nearly all of the memory usage seems to be from the "64K page pool" consumer:

For reference, here is how I executed the new stored procedure. It uses the same 1200 rows as the table:

DECLARE @t dbo.[InMemType];

INSERT INTO @t (i)
SELECT i
from [dbo].[InMem];

EXEC p2 @t;

The resulting query plan is a simple nested loop plan with no blocking operators. Per request, here is an estimated query plan for the second stored procedure.

I do not understand why memory usage grows to over 2 GB for such a query when I use a table-valued parameter. I have read various bits of documentation and in-memory OLTP white papers and can't find any reference to this behavior.

Using ETW tracing, I can see that the first procedure spends most of its cpu time calling hkengine!HkCursorHeapGetNext and the second procedure spends most of its cpu time calling hkengine!HkCursorRangeGetNext. I can also get the C source code for both procedures. The first procedure is here and the second procedure, with the memory problem, is here. However, I don't know how to read C code so I don't know how to investigate further.

Why does a simple natively compiled stored procedure use over 2 GB of memory when performing nested loops against a table-valued parameter? The problem also occurs when I run the query outside of a stored procedure.

Answer 1

When a table variable is used and accessed via a Bw-tree (range) index, memory is allocated at the start of every scan when the engine finds the starting entry (hkengine!HkCursorRangeGetFirst and hkengine!BwFindFirst). It appears that a sorted offset array is not maintained, so the rows on the first page need to be located and sorted (using quick sort, as it happens).

The memory allocation is performed using hkengine!IncrAllocAlloc, which works incrementally from a block. When a new block is needed, hkengine!IoPagePool<65536>::AllocatePage is called, which is where the 64K allocations you see come from.

This memory is not freed after the first row is found for the heap cursor.

For regular in-memory tables, the corresponding memory allocation is performed using hkengine!varAllocAlloc, which allocates from a varheap. In contrast to the table variable case, the allocations are followed by calls to hkengine!varAllocFree shortly afterward, freeing the memory.

There have been several 'memory leaks' with Bw-trees recently. For example, there are two listed in SQL Server 2019 CU 17:

A memory leak occurs under "Range Index Heap" on the in-memory table that has non-clustered indexes, whenever there are concurrent inserts.

A memory leak occurs in the range index of in-memory tables after the parallel index scan.

The memory allocations at the start of every scan in the table variable case do not leak as such, but they're not released until the table variable goes out of scope.

The cursor scan starts many times for the second and third tables in your test query as the nested loop restarts. In your case, the accumulated memory is too much and the query aborts before the memory would be freed by the variable going out of scope.

The situation is the same in SQL Server 2022 RTM, except sys.dm_db_xtp_memory_consumers does not include the 64K page pool. You can still see memory increasing in sys.dm_os_memory_clerks. It seems the 2022 arrangement is able to consume all memory available. I had to reduce buffer pool size below 2.6GB to get the OOM error. SQL Server 2019 raised an error with a 4GB buffer pool.

---

From SQL Server 2016, each hekaton table gets its own varheap(s). Among other benefits, this means a table can be scanned independent of any indexes. SQL Server 2014 had no concept of a hekaton table scan since rows were only connected through indexes. Table variables have not been updated to use the varheap scheme, so cannot support table scans.

It's possible the cursor code was updated to reflect the new varheap arrangement but overlooked the original implementation still in use for table variables.