Best Practices for Running SQL Server on vSphere

A topic that always comes up in my classes I teach is around best practices for SQL Server virtualization. Being that SQL is one of the largest virtualized business-critical apps in the world it doesn’t shock me at all. I always try to reference certain white papers that VMware has released and I noticed yesterday that they recently updated this one: Architecting SQL Server on VMware vSphere. I’ll split this post up into a few different topics that you have to think about. Another book I own and would highly recommend is Virtualizing SQL Server with VMware: Doing IT Right. This book is probably the best overall reference book on virtualizing SQL.

Before deploying SQL Server instances inside a VM on vSphere, you must understand the business requirements and the application workload for the SQL Server deployments you intend to support.

Right-sizing with SQL Server

Right-sizing is a term that means when deploying a VM, it is allocated the appropriate amount of compute resources, such as virtual CPUs and RAM, to power the database workload instead of adding more resources than are actively utilized – a common sizing practice for physical servers. Correctly utilizing right-sizing is imperative when sizing virtual machines but note that the right-sizing approach is different for a VM compared to a physical server. For example, if the number of CPUs required for a newly designed database server is eight CPUs when deployed on a physical machine, the DBA typically asks for more CPU power than is required at that time. The reason is that it is typically more difficult for the DBA to add CPUs to this physical server after it is deployed. It is a similar situation for memory and other aspects of a physical deployment; it is easier to build in capacity than try to adjust it later, which often requires additional cost and downtime. This can also be problematic if a server started off as undersized and cannot handle the workload it is designed to run. However, when sizing SQL Server deployments to run on a VM, it is important to assign that VM only the exact amount of resources it requires at that time. This leads to optimized performance and the lowest overhead and is where licensing savings can be obtained with critical production SQL Server virtualization.

CPU-sizing with SQL Server

How many CPUs should you throw at a SQL server? Is there such a thing as too many? When it comes to CPU sizing for your database VM’s, the best size is one that fits within a NUMA boundary. So for a two-socket platform, this would be a size that fits within a single socket or is easily divisible by the number of cores in a single socket. In other words, even though your ESXi host has all those cores, and you’d love to put them all to use, don’t.

If you have very large physical servers currently with many databases on them, cutting them up into smaller size VM’s that fit within a NUMA node will help improve performance significantly.

For very large VMs, it might not be possible and in that case, you can span NUMA nodes but be cautious and try to avoid doing so. Thankfully with each passing day, we’re able to scale more and more because of processor advancements.

Memory Management in SQL Server

It’s definitely no secret that SQL likes memory. One of the long-running jokes I hear is if you have free memory to give, SQL will take it and ask for more. I’d agree with that, although there are various features that VMware has that makes things easier to manage and to ensure that the SQL server has adequate resources. For example – memory reservation on the SQL VM. A reservation will guarantee the service level to the SQL Database, ensure that at least from a memory standpoint it will get the best performance possible, and it will mean the .vswp file on the vSphere will not take up any valuable storage. There is nothing worse than the horror of seeing just how much that .vswp file is on your precious SSD! I often hear that starting with a reservation of 50% is a good place to start and you can adjust from there. It’s a good mix of reserving a certain amount and then utilize a higher share value for the VM once that has been met.

Inside the guest OS, enable the local security policy “lock pages in memory” for the SQL Database service account and set trace flag -T834. This will ensure that the SQL Server instance uses huge pages on the operating system and it protects the database from any OS swapping.

Networking in SQL Server

When it comes to networking there are several things you need to consider and there is more than just the user access to the database. You need to consider management workloads, live migration for maintenance, load balancing, backup, etc. Live migration networks can struggle if the VM is sized very high and the host could have issues entering maintenance mode. A 10 Gigabit network is highly recommended for very large SQL servers because of that issue. 1 Gigabit networks simply won’t cut it. Consider using LACP and Jumbo Frames as well. If you are using Jumbo Frames enable SQL Server to use a packet size of 8192 bytes instead of the standard 4096, 8192 bytes fits into the 8972 byte TCP packet (9000 bytes with overhead included) on the wire making your transfers more efficient.

Wrap Up

To wrap up, we have covered a few of the must do’s when it comes to virtualizing SQL. I won’t pretend that I’m a SQL Administrator, very far from it actually as I’ve been on the VMware side of the house for years, but I have worked closely with SQL DBAs in the past and had the opportunity to pick their brains and find out what works and what doesn’t work. Hopefully, the tips above help you out. I’d highly recommend picking up the book I listed above if you’re going to do this at any kind of scale.