4

We are testing out a new install of SQL Server 2012 on a virtual server and the memory allocated to SQL Server seems to be capped around 16 GB even though much more is available. What could cause this?

I've tried bumping up the SQL minimum server memory setting, but the change had no effect. I've listed some of the server details below.

SQL Server 2012 Enterprise
Windows Server 2008 R2 Enterprise x64
29.93 GHz (8 processors)
77 GB RAM
Min Server Memory: 48 GB
Max Server Memory: 56 GB
Database size: 44 GB

PerfMon info:

Working Set: ~17 GB
Page life expectency: ~7,500
Target Server Memory: ~73 GB
Total Server Memory: ~15.7 GB
6
  • what is the Max server memory set to ? sp_configure 'show advanced options', 1 go reconfigure go sp_configure 'max server memory (MB)' .. If it is set to 16000 (16GB) then you have to change it to what ever you desire to be (but below 70 - 72GB to allow room for OS and other processes).
    – Kin Shah
    Apr 26, 2013 at 21:38
  • @Kin "maximum server memory is set to the default" Apr 26, 2013 at 21:40
  • 4
    How big is your database? How much load have you put against it? SQL Server will only consume memory as it needs it - it won't just grab max server memory right off the bat. Apr 26, 2013 at 21:47
  • I'm just learning about SQL Server administration, so I appreciate everyone's help. I've added some more details above. It's a test server right now, so I'm the only one in there. I'm running a query that pulls back a large set of data.
    – Jason Baek
    Apr 26, 2013 at 23:06
  • 2
    As @AaronBertrand has indicated already, SQL Server isn't going to consume memory until data is pulled into the buffer pool as a result of it being queried. If you're desperate to see the memory being consumed, and this is a test system as you've described, SELECT * every table in your database. Voilà, memory consumption >= data queried. Apr 27, 2013 at 0:25

2 Answers 2

1

SQL Server is not guaranteed to allocate the min server memory. If the load on the server never needs the minimum SQL Server will work with less - BOL http://msdn.microsoft.com/en-us/library/ms178067.aspx

As long as your configurations allow for the use of all memory you have allocated (they do), SQL Server will use what it needs, and generally no more. Since you are running a VM I would make sure you allocate 10% for the OS, remove the minimum as it can cause performance problems and let it run. If your CPU is pegged at 100%, queries are slow, and SQL Server is not allocating all of its available memory then it would be time to further troubleshoot.

-2

One additional thing to check as you are using a VM: Make sure the Hypervisor (e.g. VMware ESX) allows the VM to actually take that amount of memory from the host.

Usually this is not the case per default and stuff like "memory ballooning" comes into place to prevent the virtual OS from using all the VMs memory for its own buffer cache.

While this is usually ok for application servers, it can really hurt on virtual database servers as they think they can allocate a lot of memory more than the hypervisor will actually allow ...

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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