August 22, 2012 by Mike Hillwig
Thank you, Brent Ozar.
As a DBA, the VMWare Balloon Driver is my nemesis. It’s basically a little application that pops up, telling Windows that it needs as much RAM as possible, and Windows then takes that RAM away from SQL Server. It then takes that memory and gives it back to VMWare. This is bad. Well, it’s bad for me, but it’s good for my VMWare administrator. It allows him to put more VMs on a single host. Setting MIN memory tells Windows that I absolutely must keep a certain amount of RAM at all time. Sorry, Mr. VMWare Administrator. I’ve foiled your plot to run the entire company on a single host.
MAX memory prevents SQL Server from being too greedy with RAM. What I’ve seen in my environment is that if this number is set too high, the box will start to page. It happens when other applications are running on the server. I’m currently fighting this problem on a server right now when our backup software is picking up the daily backup files.
Something we learned the hard way is that other SQL Services (SSIS, SSRS, SSAS, and the SQL Agent) run outside of this memory reservation, so you need to make sure you leave them some breathing room as wel. Otherwise, your box will start paging, and that will suck.