SQL Server can be installed and used with VMware as a virtual server. There are some things that you will cause you to adjust your thinking with running your SQL Server on VMware, and that includes setting memory. SQL Server has two settings that help govern how much memory it will use to cache data: min server memory and max server memory. Use the two server memory options, min server memory and max server memory, to reconfigure the amount of memory (in megabytes) that is managed by the SQL Server Memory Manager for a SQL Server process used by an instance of SQL Server.
The default setting for min server memory is 0, and the default setting for max server memory is 2147483647 MB. By default, SQL Server can change its memory requirements dynamically based on available system resources. This can all change when using VMware.
With group of virtual servers running on one or more hosts, VMware does a great job of sharing memory between different virtual machines, but to do that it might have to steal memory from one virtual server to take care of another virtual server. Just because you configure a virtual server with 32GB of memory doesn’t mean the memory will always be available. If a host crashes or there is a memory hardware issue VMware might remove some memory from some guests temporarily. If your VMware hardware is configured wrong you might not have enough memory to begin with, and the memory might not be ever available, so VMware might already be doing this today.
To manage this in VMware, you can set a reservation for any virtual server memory. SQL Server starts at near-zero memory used, and then gradually caches more and more data as queries request it. Unlike most applications SQL Server’s memory needs won’t go back down, so you want to assign the proper amount of memory. You need to make sure that SQL Server gets all the memory required to operate efficiently.
When you configure new virtual servers, you need to come up with three numbers:
- The guest’s memory – this is the amount of memory the virtual server thinks it has when it starts up. In this example we’re building a virtual machine with 32GB of memory.
- SQL Server’s max memory – Best practice is to set this value at the highest value possible, after you leave 4GB of memory (or 10%) for the OS, whichever is greater. In this example, we’d set SQL Server to a max memory at 28GB, which would leave the minimum of 4GB free for the OS.
- The VMware reservation – the lowest amount of memory the guest will keep at all times. In a perfect world this is 100% of the guest’s memory, but that’s not always practical. If there is a hardware issue you would rather boot up all virtual servers with less than ideal memory than not be able to boot them up at all. For SQL Server, you generally set these reservations at 75% of the guest memory – in this example, 24GB.
So let’s assume we now have a disaster and VMware’s balloon driver fires up and now claims 25% of the memory we have assigned to our virtual server, leaving just 24GB total for the guest. This means VMware has taken some of the memory normally available to our virtual server instance and now it has just 28GB of memory (which is also our max memory setting). This is when things start swapping to disk and performance starts degrading.
That’s where SQL Server’s min memory setting is useful. You have to set the min memory in a way that accommodates your VMware reservation. If your reservation setting is only 24GB, that means that a hardware issue could cause VMware to steal 8GB of SQL Server’s memory at any time. If you still want to reserve 4GB of memory (or 10%) for the OS, that means your min memory setting should be 20GB.
The max memory number doesn’t need to change but we need to pay more attention to our min server memory number. It’s acceptable to set min memory even lower as long as reduced performance is acceptable. It is often times more acceptable for your server to have poor performance than not to run at all.
You can find additional information on these setting at Microsoft. You can also get more information at Brent Ozar and VMware.