We are trying to load a table from SQL Server Parallel DW APS to In-Memory OLTP SQL Server 2014 Enterprise edition. As we are aware that we have a constraint of 256 GB memory size and total memory on destination server is 500 GB. DBA has allotted 90% of the memory for our operation out of that we can only use 256Gb. The source table size is 30GB(192 million records, 320 columns). Every time when we try increasing the memory on the destination still we keep on getting error:
There is insufficient system memory in resource pool "Default" to run this query
The disk space of in memory DB is 1TB. There were other tables with 10Gb were able to fit in. The table is being loaded to in memory using SSIS Visual Studio 2013. Oledb source, Oledb destination (no boxes checked, Rows per batch 5000, max commit size 214748), network packet size 2000 on connection managers. Cannot check table lock as it does not all for in memory oltp table.
The destination table has Durability SCHEMA_AND_DATA. Every time we get the above error. We drop and recreate the destination in memory table and make sure the row count is 0 before loading. How can we over come this error in 2014 and will we be able to do this in SQL Server 2016? We have to allot twice the size of source table for in-memory operation.