3

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:\Artash\LocalBGD\Data\memory_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.

3
  • So you already have household_mem_old of that size and now you are trying to create the SECOND memory optimized table of the same size?
    – sepupic
    Oct 4, 2017 at 12:43
  • @sepupic No, I just want to convert my Household_mem table to memory optimized. That is why first I do rename, and then I am creating memory optimized table and populating data in it. Oct 4, 2017 at 12:46
  • @sp_BlitzErik I tried to split by big insert into batches. In each batch I was inserting 1mln rows. 140 batches completes successful and then I get the error. Currently my table size is 8,544MB and index size is 1,715MB. Oct 4, 2017 at 13:41

1 Answer 1

0

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.

6
  • 1. 16Gb of memory, and all allocated to SQL Server. 2. Yes I have binded the database to the recource group. 3. Microsoft SQL Server 2016 (SP1-CU5) (KB4040714) - 13.0.4451.0 (X64) Sep 5 2017 16:12:34 Copyright (c) Microsoft Corporation Enterprise Edition (64-bit) on Windows 10 Pro 6.3 <X64> (Build 14393: ) 4. 8388608 - from sys.dm_os_sys_info 5. No compresed data or indexes. Oct 4, 2017 at 12:41
  • @artashes - if you have 16GB of server memory, you cannot allocate all of it to SQL Server 'max memory' setting, because you can/will starve the OS. So you need to leave room for the OS, subtracting that amount from the total server memory, and then decide what you will allocate to SQL Server 'max memory'. And from that, 75% can be allocated to In-Memory OLTP. Link here: bit.ly/2rIT4um
    – NedOtter
    Oct 4, 2017 at 14:15
  • @NedOtter - I know what are you talking about, but I will receive another error in case of OS problems. Currently my table is 10GB in size, so I have more 6GB available to SQL Server, but when I insert 1mln rows, I get "There is insufficient system memory in resource pool 'Pool_IMOLTP' to run this query." Oct 4, 2017 at 14:26
  • 1
    @Artashes - There is no magic here - you need enough memory not only to migrate your data, but also to do your workload, i.e. create row versions, temporal staging table(s), etc. You said that the value of committed_target_kb = 8388608, which is 8GB. With 16GB for SQL , you can only use 75% of the 8GB, which = 6.1GB. Based on these numbers, there is no way 10GB of data can be migrated - you simply don't have enough memory.
    – NedOtter
    Oct 4, 2017 at 15:14
  • @NedOtter - Sorry for confusing answer, a run the query in wrong database. The actual committed_target_kb/1024 = 16000. I cant edit my comment. Oct 5, 2017 at 6:42

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