March 28, 2017 at 7:53 am
Trying some tests in moving a table from disk to in memory. Using script or GUI I get an error when loading the data "insufficient memory in resource pool 'internal' Have 16 gig on this machine loading about 65 million rows.
March 28, 2017 at 8:20 am
tcronin 95651 - Tuesday, March 28, 2017 7:53 AMTrying some tests in moving a table from disk to in memory. Using script or GUI I get an error when loading the data "insufficient memory in resource pool 'internal' Have 16 gig on this machine loading about 65 million rows.
Error message seems reasonable. What is your question?
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
March 28, 2017 at 9:07 am
Wonder how many people have run into this and what limits they have seen. When using the GUI wizard or script I get the same. It tells me in the wizard it will only use 20 meg of memory, so with 16 gig of memory I thought I was safe. It chokes loading the data, I would have thought the wizard would have inspected that when analyzing the table to move.
March 28, 2017 at 9:39 am
The limit is that the data must fit into memory. What's the total size of those 65 million rows?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 28, 2017 at 9:48 am
The analyzer told me it would be 20 megabytes we have 16 gig. Interesting thing I also found out, I needed to move the LDF file for this database to a different drive letter. So I did the simple detach and copy LDF file to different drive. Could no reattach the database would not let me find the log file in the gui. Found this nice error if you have a memory oltp table in database can't do this.
March 28, 2017 at 9:58 am
What's the total size of those 65 million rows?
Log file doesn't have to be on a separate drive for In-Memory OLTP. I've had successful tests with everything in one place.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 28, 2017 at 10:07 am
Let me clarify a few things.
1. This is a first attempt on dev
2. The error was when I tried to move the ldf file for this database to a different drive
3.total space for table is 6826784 KB, memory should not be issue
March 28, 2017 at 12:10 pm
To clarify, when you say "16 GB" is that the memory allocated to the resource pool or to the Server?
Does your resource pool have enough space for that 7 GB table? The OS would need at LEAST 7 GB free to hold a 7 GB table, plus space for the OS and the SQL Server Instance. OS should have at least 4 GB (based on everything I have ever read about Windows), so that leaves your SQL to hold 5 GB of that 16 in order to have space for your 7 GB table. using 100% memory is NEVER recommended as you will likely crash the OS. So, for your setup, your SQL OS is going to be using 4 GB of memory (to have 1 free) to make that work. Your queries will likley fail as SQL doesn't have enough memory to operate efficiently. I'd double that memory if you want to throw a 7 GB table at it.
Now if you have 16 GB allocated to the resource pool, do you have enough physical memory to work with that?
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply