Cranky Series: Set Min and Max RAM

2

August 22, 2012 by Mike Hillwig

Thank you, Brent Ozar.

This is something I learned from Brent when he was talking about running SQL Server in a virtual environment.

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.

  • As a young Sys Admin/accidental DBA in a very large VM environment, This setting alone has fixed so many issues. I have had to clean up and optimize a lot of different SQL server and one of the most common mistakes during config is to leave the Default memory config. By simply making the config changes as mike talks about in this article any young DBA can look like a genius..

    Also Defaults != best practice !

  • Pingback: How Not to be a Cranky DBA: The Blog Series | Cranky DBA()