This morning I got a phone call from a colleague requesting insights as to why an instance of SQL Server 2005 was not using more than 3.5 GB of memory. The machine had 12 GB installed, /pae and /3gb were set in boot.ini (Windows Server 2003 Enterprise), and Lock Pages In Memory was set. All seemed fine for a 12 GB system.
We confirmed that SQL Server was using 3.5 GB via Perfmon counters and "DBCC MemoryStatus". Task Manager showed only 128 MB free on the server and that 12 GB was installed and visible to Windows, and that SQL Server was using about 256 MB (the 3.5 GB of AWE being hidden from Task Manager, which only shows VAS memory). Obviously something was taking up the extra 9 GB, but what? AWE memory isn’t the easiest to view under 32 bit Windows, so unless you know what is using it, you’re in for a search (let me know if you know an easy way to view applications using AWE). My initial guess was a second copy of SQL Server running with AWE, but that wasn’t the case.
The server happened to be running under VMWare, and the issue was the existence of the VMWare Balloon Driver, which runs as vmmemctl. VMWare is able to share identical memory between machines, so if the balloon driver reserves memory identically over a number of machines, the host only has to store one copy of this in memory. This can give a substantial lift to memory usage efficiency.
What the balloon driver does is to take up memory to simulate memory pressure on the virtualised server. Windows running on the guest OS then determines what is unnecessary to be in memory and pages it to disk. If the server requires more memory, the balloon should deflate a little. This ensures that the guest OS only has as much memory as it needs, and very little is wasted.
However, what happens when SQL Server starts? If there’s insufficient memory available, AWE won’t kick in at all, and SQL Server will be limited to VAS – 3GB in this case, due to the /3gb switch in boot.ini. However, as SQL Server is using AWE and 3.5 GB, the case is slightly different. What has happened here is SQL Server has detected enough free memory to start AWE, and grabbed as much as it can – 3.5 GB. However, the balloon driver now conspires to keep memory pressure on, and as SQL Server believes that there is no free memory available, it will not increase its memory usage.
The balloon driver works well for almost all applications that blindly request more memory and are happy to let Windows manage memory. SQL Server is different in that it actively manages its own memory, and will only request more if it believes the operating system can supply it. I’d recommend not using a balloon driver on a production SQL Server installation. Actually, I’d recommend not virtualising your SQL Server installation at all, if possible, but there are high availability reasons that may make it appropriate.
Thanks! I didn’t know about AWE memory
Hi, I’m wondering if you have had any new experience with this. We don’t really want to disable the balloon driver because this is very bad for other VMs on the host, but we are keen to continue using VMware as part of our strategy.
What are your thoughts about disabling the AWE memory in SQL? is there any problem with doing that?
interestingly, an article I found on AWE memory from Microsoft said that AWE shouldn’t be used in applications in future because its a feature that wont be available in future versions!
You need to remember that the balloon driver only kicks in when there is contention for resources on the host. Chances are that if ballooning is occurring you need to look at the host resources – you may need to increase the amount of physical memory.
I don’t really condone using memory reservations, but this is an option you could use to guarantee your SQL VM has enough resources assigned to it. Reservations can be set in vCenter.
I would suggest lookign at these before you modify the bahaviour of your SQL install.