Last Updated on January 24, 2023 by John Morehouse
In today’s modern world, virtualization is often used to help mitigate cost as well as to provide flexibility in scaling for performance workloads. It’s very easy to scale a virtual machine with a few mouse clicks and a brief outage while it reboots. If you are running VMWare on-premises, this is a few minute outage. If you are running in the cloud, you can simply resize the virtual machine to a higher compute tier and upon reboot, poof, you have more compute.
If you are running locally, one thing we see often crop up are noisy neighbors. In the virtualization world, all of the virtual machines on a given host share the compute resources. This means that CPU and memory can be “swapped” back and forth between virtual machines as their work load demands. If you have a number of virtual machines that all require the compute, then that creates a bottle neck. Thankfully, there are ways to help prevent this.
If you are running SQL Server in a virtual environment, memory is king. SQL Server loves to have as much memory as possible and will potentially fit your entire workload into cache if feasible. However, if you have a noisy neighbor, memory can become scarce and you could potentially start to see memory pressure within SQL Server. Since the hypervisor has to balance resources, both CPU and memory utilization, across multiple virtual machines, it does have the ability to steal memory away from the guest operating system which effective can steal memory from SQL Server.
One way to help mitigate this is to ensure a memory reservation is in place at the VM level. A memory reservation effectively allocates all of the required memory from the host to that specific virtual machine. This helps to ensure that the virtual machine gets all the memory its been allocated and further swapping shouldn’t occur.
Note: If the VM is running, you’ll have to stop it to make this change.
Once the virtual machine is stopped, you can open the configuration within vSphere or whatever flavor of hypervisor you are using. As shown above, the virtual machine has been allocated 16GB of memory. Simply select the box to “reserver all guest memory” and then click Ok.
You can then start the virtual machine and hopefully quiet down the neighbors.
Summary
In this post I discuss a quick way to help prevent memory ballooning is by implementing a memory reservation for the virtual machine. This will effectively allocate all of the requested memory to the guest VM and furthermore prevent the hypervisor from being able to reclaim it. Of course, your mileage may vary, but implementing a memory reservation for your SQL Server virtual machines would be a best practice in my book.
Happy SQL’ing!
© 2022 – 2023, John Morehouse. All rights reserved.
The post Preventing Noisy VM Neighbors for SQL first appeared on John Morehouse.