Be aware of 701 error if you use memory optimized table variable in a loop

In blog “Importance of choosing correct bucket count of hash indexes on a memory optimized table”,  I talked about encountering performance issues with incorrect sized bucket count.  I was actually investigating an out of memory issues with the following error.

Msg 701, Level 17, State 103, Line 11
There is insufficient system memory in resource pool 'default' to run this query.

I simplified the scenario but customer’s code is very similar to the loop below.  Basically, this customer tried to insert 1 million row into a memory optimized table variable and process them.  Then he deleted the rows from the memory optimized table variable and inserted another 1 million.  His goal was to process 1 billion rows.  But before he was able to process 1 billion rows, he would run out of memory (701 error as above)

DECLARE @t2 AS [SalesOrderDetailType_inmem]
insert into @t2 select * from t2

while 1 = 1  --note that this customer didn’t use 1=1. I just simplified to reproduce it
begin
       delete @t2
       insert into @t2 select * from t2
end

This customer was puzzled because he delete existing rows. At any given time, there should not be more than 1 million rows.  SQL Server should not have run out of memory.

This is actually by-design behavior documented in “Memory-Optimized Table Variables”).  Here is what is state “Unlike memory-optimized tables, the memory consumed (including deleted rows) by table variables is freed when the table variable goes out of scope)”.  With a loop like above, all deleted rows will be kept and consume memory until end of the loop. 

 

Complete Repro
=============

Step 1 Create  a disk based table and populate 1 million rows

CREATE table t2(
  [OrderQty] [smallint] NOT NULL,
  [ProductID] [int] NOT NULL,
  [SpecialOfferID] [int] NOT NULL,
  [LocalID] [int] NOT NULL
  )

Step 2 create a type using memory optimized table

CREATE TYPE [SalesOrderDetailType_inmem] AS TABLE(
  [OrderQty] [smallint] NOT NULL,
  [ProductID] [int] NOT NULL,
  [SpecialOfferID] [int] NOT NULL,
  [LocalID] [int] NOT NULL,

  INDEX [IX_ProductID] HASH ([ProductID]) WITH ( BUCKET_COUNT = 1000000),
  INDEX [IX_SpecialOfferID] NONCLUSTERED (LocalID)
)
WITH ( MEMORY_OPTIMIZED = ON )

Step 3 run the following query and eventually you will run of memory
DECLARE @t2 AS [SalesOrderDetailType_inmem]
insert into @t2 select * from t2

while 1 = 1
begin
       delete @t2
       insert into @t2 select * from t2
end

 

Jack Li |Senior Escalation Engineer | Microsoft SQL Server

twitter| pssdiag |Sql Nexus