asked
SQL Server In-memory OLTP 2014 running out of memory
Hi All, 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 inmemory DB is 1TB. There were other tables with 10Gb were able to fit in . The table is being loaded to inmemory 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 inmemory oltp table. The destination table has Durability SCHEMA_AND_DATA.Every time we get the above error. We drop and recreate the destination inmemory 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.
hi Thomas, We decided to move this to in memory 2014 considering the table size in DB. But this table is compressed so it looks to be 30 GB. We downloaded this data to csv and the file size turned out to be 174GB. Size of inmemory is allocated based on the size on disk. 174Gb is still a huge amount to fit in 256 GB memory where some space needs to be for other tables too. So we are not going to move this table to in-memory. Thanks for your help Thomas.
A few questions: 1. Target Server - physical or virtual? 2. Target Server - what version / edition of Windows Server? 3. Source table - what's the schema for the table? 4. You say you have successfully loaded other tables into memory; are these tables still loaded when you attempt to load this big table? If so, have you tried unloading those tables before loading this biggie? Also... Check in-memory / memory-optimised table memory consumption carefully against your suppositions: select * from sys.dm_db_xtp_table_memory_stats --where object_id = object_id('')
Thanks For Your reply Thomas. 1. Target Sever is Physical Server. 2. WindowsVersion : Windows 2012 R2 no SP.CPU 24 core 2.60 GHZ,524129 MB 3. Source table: Dbo in APS is the schema. 4. Those tables are pretty small and they are still loaded The big table is still loaded and has the below results from the query. `object_id memory_allocated_for_table_kb memory_used_by_table_kb memory_allocated_for_indexes_kb memory_used_by_indexes_kb` `18099105 NULL 210144222 4685056 881831`
by `schema` I meant the columns / data types... sorry, I wasn't clear...
18 People are following this question.
