This post has been republished via RSS; it originally appeared at: Microsoft Tech Community - Latest Blogs - .
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:
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.
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;
Now we went ahead and deleted all data from the table (with the columnstore Index) and ran the same query above:
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