SQL Server – Memory-Optimized Filegroup Takes 1GB on Disk After Converting Table to In-Memory

sql serversql server 2014

SQL Server 2014 SP2. As the titles says, we have converted one of our database tables to be in-memory. After we did this, the corresponding memory-optimized filegroup takes up 1GB on the disk, but on a larger server it's up to 4GB). I suspect it has to do with the number of CPUs.

The table is EMPTY! The structure of the table is nothing special, something like this:

Id  int
StatusId    int
ReviewRequired  bit
SourceDate  datetime
UserID  int
InsertDate  datetime
UpdateDate  datetime

Why does it take up so much space on the disk? I looked, but I couldn't find any way to control the size of the memory-optimized files created.

Here is T-SQL Script used for creating in-memory table:

    CREATE TABLE [dbo].[MyTable]
(      [Id] [INT] NOT NULL,
       [StatusId] [INT] NOT NULL,
       [Reviewed] [BIT] NOT NULL,
       [CreateDate] [DATETIME] NOT NULL,
       [RequestID] [INT] NULL,
       [InsertDate] [DATETIME] NULL,
       [UpdateDate] [DATETIME] NULL,
CONSTRAINT [PK_Id] PRIMARY KEY NONCLUSTERED HASH 
(
       [Id]
)WITH ( BUCKET_COUNT = 16),
INDEX [IX_StatusId] NONCLUSTERED HASH 
(
       [StatusId]
)WITH ( BUCKET_COUNT = 64)
)WITH ( MEMORY_OPTIMIZED = ON , DURABILITY = SCHEMA_AND_DATA )

Best Answer

I would like to close the loop on my own question. We worked with Microsoft support on this and the bottom line is : "it's by design". I strongly believe that this is a flawed design and should be changed. The Microsoft support engineer opened a feature change request for this. In addition I created a "MS Connect" item about this. Please up-vote it if you agree with the request: https://connect.microsoft.com/SQLServer/feedback/details/3116296/enabling-in-memory-feature-for-databases-takes-up-huge-amount-of-disk-space-for-checkpoint-file-pairs-cfps-in-cases-when-there-are-many-databases-on-a-sql-server-instance

Related Question