September 28, 2016 at 9:00 pm
hi,
i'm currently testing SQL Server 2014 in-memory-oltp in separate virtual environment with virtual hardware :
16 core CPU (Intel Xeon based)
256GB RAM with 250GB allocated for SQL Server
Windows Server 2012 Datacenter edition (should support upto 4TB RAM)
SQL Server 2014 SP2 Enterprise edition
i created 2 new tables with memory optimization "on" to hold our biggest table (around 20GB and 15GB)
the problem is, it looks like SQL Server can't use more than 32GB of RAM
is there any other settings i should change? (i attached my current setting and memory usage query)
Thank You
September 29, 2016 at 3:26 am
Enterprise edition can use OS maximum of memory, so more than you have.
What do you mean by "it looks like SQL Server can't use more than 32GB of RAM"? What problems or errors are you seeing?
Oh, and 250 allocated to SQL is far too much with 256 GB on the server, you're at risk of starving the OS. With 256GB of memory, you need a lot more than 6GB allocated to the OS. I'd suggest dropping SQL to 224 or something in that area, and then monitoring the server's Available Memory perfmon counter to make sure it stays around 1GB.
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
September 29, 2016 at 11:26 pm
GilaMonster (9/29/2016)
Enterprise edition can use OS maximum of memory, so more than you have.What do you mean by "it looks like SQL Server can't use more than 32GB of RAM"? What problems or errors are you seeing?
Oh, and 250 allocated to SQL is far too much with 256 GB on the server, you're at risk of starving the OS. With 256GB of memory, you need a lot more than 6GB allocated to the OS. I'd suggest dropping SQL to 224 or something in that area, and then monitoring the server's Available Memory perfmon counter to make sure it stays around 1GB.
no error but it looks like the RAM usage "stuck" at 32GB
but it turns out my queries didn't use any others table beside both in-memory tables i created earlier, so MSSQL didn't use any more RAM for buffer 🙂
i tried query other table, the RAM usage went up as expected (up to 46GB so far)
hmm i thought OS RAM requirements only around 4GB, that's why i only give 6GB
thank you, at least my earlier "problem" solved 🙂
September 30, 2016 at 1:52 am
ils_83 (9/29/2016)
hmm i thought OS RAM requirements only around 4GB, that's why i only give 6GB
The minimum RAM requirement for installing the OS is 4GB. Windows needs memory to manage memory, so the more you have, the more Windows needs to manage that memory properly.
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
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply