In-memory table in Azure SQL DB doesn’t release memory- Msg 41823, Level 16, State 109, Line 1
Published Oct 07 2023 02:11 AM 2,250 Views

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

 

Version history
Last update:
‎Oct 07 2023 02:11 AM