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

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

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::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.

StackExchange Database Administrators Q#319909, answer score: 17