Memory-Optimized off-row storage issue
I have a problem because of data row-size of some tables greater than 8060bytes, so there was some off-row storage in internal xtp DMVs and those tables allocated a memory about 120GB.
I alter that tables and reduce row-size to less than 8060bytes, the memory allocated to those tables reduce and the allocated memory to object_id=0 is being grow in sys.dm_db_xtp_table_memory_stats. I think The garbage collector thread cant find this orphaned object , thus it cant deallocate memory.
another issue is 'Memory Allocated To Memory Optimized Objects' is 25GB where 'Memory Used By Memory Optimized Objects' is 62GB,
I offline and then online database and reset SQL Server Service but the allocate memory doesn't change.how do I can deallocate this memory ?
The version is SQL Server 2016 SP2-CU4.
I simulate the scenario :
---------------------------------------------
USE master
GO
CREATE DATABASE InMemDB ON PRIMARY
( NAME = N'InMemDB', FILENAME = N'D:\Databases\2017\InMemDB.mdf' , SIZE = 4096KB , FILEGROWTH = 1024KB )
LOG ON
( NAME = N'InMemDB_log', FILENAME = N'D:\Databases\2017\InMemDB_log.ldf' , SIZE = 2048KB , FILEGROWTH = 10%)
GO
ALTER DATABASE InMemDB ADD FILEGROUP InMemDB_FG CONTAINS MEMORY_OPTIMIZED_DATA
ALTER DATABASE InMemDB ADD FILE (name='InMemDBDF', filename='D:\Databases\2017\InMemDB') TO FILEGROUP InMemDB_FG
ALTER DATABASE InMemDB SET MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT=ON
GO
USE InMemDB
GO
DROP TABLE IF EXISTS memory_optimized_table
CREATE TABLE memory_optimized_table
(
id INT NOT NULL IDENTITY(1,1) PRIMARY KEY NONCLUSTERED,
col1 VARCHAR(8000),
col2 VARCHAR(8000),
col3 VARCHAR(8000),
col4 VARCHAR(8000),
col5 VARCHAR(8000)
) WITH(MEMORY_OPTIMIZED = ON , DURABILITY = SCHEMA_AND_DATA)
-- there is no object_id= 0
SELECT * FROM sys.dm_db_xtp_table_memory_stats
-- there is some off-row storage
SELECT I.*,C.name AS Column_Name
FROM sys.memory_optimized_tables_internal_attributes I
LEFT OUTER JOIN sys.columns C
ON C.object_id = I.object_id
AND C.column_id=I.minor_id
WHERE I.object_id = object_id('memory_optimized_table')
SET NOCOUNT ON
INSERT INTO dbo.memory_optimized_table
(
col1,
col2,
col3,
col4,
col5
)
VALUES
(
REPLICATE('a', 8000),
REPLICATE('a', 8000),
REPLICATE('a', 8000),
REPLICATE('a', 8000),
REPLICATE('a', 8000)
)
GO 30000
-- there is no object_id= 0 too
SELECT * FROM sys.dm_db_xtp_table_memory_stats
-- I reduce row-size to less than 8060Bytes to remove Off-Row Storage
ALTER TABLE dbo.memory_optimized_table
DROP COLUMN
col2
, col3
, col4
, col5
-- the off-row storage has been removed and memory allocation to memory_optimized_table deallocated
SELECT I.*,C.name AS Column_Name
FROM sys.memory_optimized_tables_internal_attributes I
LEFT OUTER JOIN sys.columns C
ON C.object_id = I.object_id
AND C.column_id=I.minor_id
WHERE I.object_id = object_id('memory_optimized_table')
-- but allocated memory with object_id = 0 is 964687KB, that doesn't deallocate !
SELECT * FROM sys.dm_db_xtp_table_memory_stats
----------------------------------------------------------
Ehsan Hosseinpour
shared this idea
2 comments
-
Devin Rider
commented
Hi Ehsan,
The issue you’re describing has to do with how SQL Server manages the internal backing tables for memory optimized tables with large object and off row data. In down level editions of SQL Server, the engine does not correctly drop these internal tables when the user alters these columns. This issue has been addressed in the latest version of SQL Server.
The mitigation is to create a new database, migrate the data from the old database to the new database, and drop the old database. When you drop the old database, the leaked internal tables are freed, and the memory reclaimed. We also recommend that you upgrade to the latest version of SQL Server as otherwise, the leak can re-occur if you alter the columns again.
This KB is not very informative and says this only applies for Linux, but as you’ve demonstrated the issue also applies to SQL Server running on Windows:
Devin
-
Anonymous
commented
:-(