Vryjdfkk

I have a table that I want to convert to memory optimized.

I have 160 million rows in my table. According to this topic my table will need:

(24B(header size) + 8B(IndexPointer) + 30B(dataRowSize)) * 160mln = 9460.45 MB of memory to store the table and 4B*160mln = 610.35 MB memory to store the index.

Total memory need is about 10GB.

I gave 16,000MB memory to my SQL Server, and I think this might be enough, but when I try to populate my table with data I am getting "out of memory"

I use the following script to change my disk based table to memory-optimized:

USE [master]

GO



ALTER DATABASE [BGD] ADD FILEGROUP [memory_optimized_filegroup_0] CONTAINS MEMORY_OPTIMIZED_DATA 

GO



ALTER DATABASE [BGD] ADD FILE ( NAME = N'memory_optimized_file_122569704', FILENAME = N'E:ArtashLocalBGDDatamemory_optimized_file_122569704' ) TO FILEGROUP [memory_optimized_filegroup_0]

GO



USE [BGD]

GO



EXEC dbo.sp_rename @objname = N'[dbo].[household_mem]', @newname = N'household_mem_old', @objtype = N'OBJECT'

GO



USE [BGD]

GO



SET ANSI_NULLS ON

GO



CREATE TABLE [dbo].[household_mem]

(

    [location2id] [SMALLINT] NULL,

    [location3id] [SMALLINT] NULL,

    [location4id] [SMALLINT] NULL,

    [location5id] [SMALLINT] NULL,

    [location6id] [INT] NULL,

    [location7id] [INT] NULL,

    [location8id] [INT] NULL,

    [location9id] [INT] NULL,

    [povertyscore] [SMALLINT] NULL,

    [householdid] [INT] IDENTITY(1,1) NOT NULL,



 CONSTRAINT [household_mem_primaryKey]  PRIMARY KEY NONCLUSTERED 

(

    [householdid] ASC

)

)WITH ( MEMORY_OPTIMIZED = ON , DURABILITY = SCHEMA_ONLY )



GO



SET IDENTITY_INSERT [BGD].[dbo].[household_mem] ON 



GO



INSERT INTO [BGD].[dbo].[household_mem] ([location2id], [location3id], [location4id], [location5id], [location6id], [location7id], [location8id], [location9id], [povertyscore], [householdid]) SELECT [location2id], [location3id], [location4id], [location5id], [location6id], [location7id], [location8id], [location9id], [povertyscore], [householdid] FROM [BGD].[dbo].[household_mem_old] 



GO



SET IDENTITY_INSERT [BGD].[dbo].[household_mem] OFF 



GO



Caution: Changing any part of an object name could break scripts and stored procedures.

The statement has been terminated.

Msg 701, Level 17, State 109, Line 47

There is insufficient system memory in resource pool 'default' to run this query.

I have also tried to add a resource pool for my database but still get the same error.

Microsoft recommends 2x data/index size as a starting point for memory allocation, but that's a generalization.

Is any of the data and/or indexes in the source table compressed?

How much memory on the server?

How much memory allocated to SQL Server?

When you created the resource group, did you bind the database to it, and then offline/online the database to make the binding effective? What percent did you allocate to the resource group?

Please post the result of:
SELECT committed_target_kb
FROM sys.dm_os_sys_info

And:
SELECT @@version

Agree with @sp_blitzErik that an atomic insert of 100 million rows to a memory-optimized table is not the best way to migrate data, but not necessarily because it will require less memory - batching will perform much better.