In-memory table in Azure SQL DB doesn’t release memory- Msg 41823, Level 16, State 109, Line 1

by | Oct 7, 2023 | Technology | 0 comments

This article is contributed. See the original author and article here.

Issue


We recently encountered a support case where a customer using In-memory tables in an Azure SQL DB, receives an error message while trying to insert data into the table that also has a clustered columnstore index. The customer then deleted the entire data from the In-memory Tables (With the clustered columnstore index), however it appeared that the Index Unused memory was still not released. Here’s the memory allocation the customer could see:


Tanayankar_Chakraborty_0-1696653811279.png


 


Error


In addition to the error above- here is the error text:


Msg 41823, Level 16, State 109, Line 1


Could not perform the operation because the database has reached its quota for in-memory tables. This error may be transient. Please retry the operation. See ‘http://go.microsoft.com/fwlink/?LinkID=623028‘ for more information


 


Workaround


To reproduce the issue, we created two tables in our premium tier Azure SQL DB, one with a clustered columnstore Index while the other just had a regular clustered index. Also, the columnstore index was created with the option- MEMORY_OPTIMIZED=ON.


Tanayankar_Chakraborty_1-1696653900880.png


Then we went ahead and inserted data in both the tables and ran the script below to find the memory consumption of the indexes (Notice the 97 MB reported by the Index_Unused_memory column below in the table containing the columnstore Index):


 


IF(    SELECT COUNT(1)    FROM sys.data_spaces    WHERE type = ‘FX’) > 0


    BEGIN


        SELECT OBJECT_NAME(object_id) AS tblName,


               CAST(memory_used_by_table_kb / 1024.00 AS DECIMAL(10, 2)) [Total used Memory MB],


               CAST(memory_allocated_for_table_kb / 1024.00 AS DECIMAL(10, 2)) – CAST(memory_used_by_table_kb / 1024.00 AS DECIMAL(10, 2)) [Total Unused Memory MB],


               CAST(memory_used_by_indexes_kb / 1024.00 AS DECIMAL(10, 2)) [Index used Memory MB],


               CAST(memory_allocated_for_indexes_kb / 1024.00 AS DECIMAL(10, 2)) – CAST(memory_used_by_indexes_kb / 1024.00 AS DECIMAL(10, 2)) [Index Unused Memory MB]


        FROM sys.dm_db_xtp_table_memory_stats


        ORDER by 2 desc;


    END;


 


Tanayankar_Chakraborty_2-1696653977445.png


 


Now we went ahead and deleted all data from the table (with the columnstore Index) and ran the same query above:


Tanayankar_Chakraborty_3-1696654016940.png


 


Tanayankar_Chakraborty_5-1696654058418.png


 


The test above proves that it is not the data contained in an In-memory table that consumes the memory, but it is rather the Columnstore Index that consumes the memory and occupies it till the index stays on the table. Even if we delete the data from the table, the memory will still remain in the Index Unused memory. The only possible option to release the Index Unused memory is to drop the clustered Columnstore Index.


Moreover, it is also recommended to use a Columnstore Index only for tables with a lot of data (Millions or even billions) only if using it helps achieve the overall performance levels expected.


 


References


In-Memory OLTP in Azure SQL Database | Azure Blog | Microsoft Azure


In-memory technologies – Azure SQL | Microsoft Learn


Should table or stored procedure be ported to in-memory OLTP – SQL Server | Microsoft Learn


 

Brought to you by Dr. Ware, Microsoft Office 365 Silver Partner, Charleston SC.

Submit a Comment