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.