0

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 )
  • 1
    Any nonclustered indexes on that table? What size hashbucket did you use for that? If you look at all objects that reside within that filegroup (including internal objects) what size do they make up? Or is it just that table? – Nic Nov 29 '16 at 23:57
  • 3
    In-memory OLTP pre-allocates the data files on create rather than waiting for you to populate the data. Think of it as an empty container waiting for data, and when you populate the data, you don't have to wait for the allocation. Like creating an empty database with 10GB data files. – Aaron Bertrand Nov 30 '16 at 0:12
  • Thank you, Nic and Aaron. I understand it per-allocates the space, but based on what? Why would it pre-allocate up to 4GB of data for an empty table. With 100's of databases on the server, this is potentially disastrous. – SQL_Guy Nov 30 '16 at 0:17
  • 3
    "That is because the hash indexes in the sample are pre-sized for a larger data size. Note that hash indexes have a fixed size, and thus their size will not grow with the size of data in the table." So it seems the data size is pre-allocated based on the width of the row and how many rows it expects (which is a function of bucket size of course). – Aaron Bertrand Nov 30 '16 at 0:43
  • Once again, thank you Aaron and Nic, for commenting on this. I added a script above showing how the in-memory table was created. As you can see the number of buckets is very small (16 and 64) and, as I mentioned, the table has only a few hundred records at the most. Why would SQL Server take up 4GB of disk space for something like this is beyond me. The worst part is that once in-memory filegroup is created you can't get rid of it, but that's another story. – SQL_Guy Nov 30 '16 at 18:31
0

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.