VMware and SQL Server Best Practices.

I wish you knew a few things about VMware and SQL Server best practices. If you’ve ever asked yourself “Why does my storage seem slower on my SQL Server VMs?!”, this post may help. We love VMware in our SQL Server consulting practice. But there are some things you should know and do first. These aren’t deep and mysterious secrets. But they’re things we see a lot of folks still missing out there.

I’m going to start doing a series of quick blog posts in the “What We Wish You Knew” tag here on the site this month and see if I can make a habit out of it and keep the posts going in 2021.

This series will stay deliberately to the point (for a change.) A lot of this information is available all over the place – but I’m sharing it because we’re still bumping into it the topic of the post in the wild and I’d rather you find it and fix it yourself and use us for the more fun and challenging issues!

VMware and SQL Server Best Practices

I’ve lost track of the number of times I’ve seen a few violations of what we’re listing below. And at Straight Path, I don’t just mean we’ve seen the “findings” – but we’ve seen them, we’ve worked with our clients to fix them, and we’ve seen, sometimes significant, performance improvements.

Using the PVSCSI Controllers (… and More than one.)

Want to improve IO performance on your SQL Server VM and reduce CPU overhead?

If you are presenting your storage up through data stores to VMDKs and you accepted a standard configuration – you probably have one SCSI controller for your VMs – the LSI SAS controller.

You will normally see both an improvement in IO performance AND a reduction in CPU hit by switching over to the PVSCSI controllers.

If you’ve created separate VMDKs for OS/Data/Logs/TempDB/Binaries it would be a good idea to consider adding 2 PVSCI adapters and splitting the Data/Logs/TempDB at least to them. Leave the OS partition where it is on the LSI SAS (trust me!)

This VMware KB article talks about how. It’s fairly easy. Requires a power down. Leave the C:\ drive alone – no need to move it.

Sockets and Cores on VMware for SQL Server: What difference does the ratio make?

We see folks having some issues related to Socket counts and core counts. There is one very obvious issue – I’ve lost track of the number of VMs running SQL Server Standard (which I prefer if you can do it!) with 8 cores, but 1 core per socket – meaning you are using 8 sockets – 4 more than SQL Server standard supports. We’ve helped many clients optimize performance simply by fixing this ratio.

There is a fairly comprehensive blog post at VMware which will go a bit deeper but a few thoughts:

• Don’t exceed the sockets of your host.
• Keep the license limitations in mind. In general, more cores per socket is better – but look at the blog post from VMware for more.
• Align your cores and sockets to the host.
• Consider memory for NUMA boundaries – if you are using less than half of the RAM on a 2 socket box, the only CPU is a concern; if you are exceeding – then you might as well have your cores spread across 2 sockets even if you don’t need them to align CPUs.

Check this VMware blog post out as a good start.

Can I have too many cores on my SQL Server VM?

Yes, you can. Co-Stop and Ready Time waits are the arch-enemies of SQL Server performance on a VM. You get this from too many guests on a host with too much CPU allocated. You also get it by allocating more cores than you need. Remember – you are in a virtual world now. You can start smaller and add. Try it! Keep the socket: core ratio from above in mind – but start lower and add. You’ll find it easier to do that than take away once you add. You’ll also find that by reducing the number of cores and running that VM a little “hotter” than maybe you would otherwise, you ask the ESXi host for fewer cores to be ready – which means less waiting on these waits.

You should have a solid VMware monitoring tool. We know a thing or two about VMs here, and we can help clients out – but we aren’t VMware admins – for us, the V-Sentry feature/product built into Sentry One is PERFECT. We can see noisy neighbors. We can see which hosts are busy. We can see which VMs on a host are consuming most of the CPU/IO/Memory. And we can see when ready time and co-stop waits occur.

Noisy Neighbors – and the wrath they bring your SQL Servers

Virtualization is a powerful tool. And it’s great. But we’ve often seen a SQL Server stacked onto a host with far too many VMs. And we’ve seen the DRS/Vmotioning of VMs killing SQL Server performance. Know your neighbors. See what else is on the host. I’m not saying you need 1:1 ratios (though, sometimes you may!) but you shouldn’t have your mission-critical SQL Server as just one of the VMs on a crowded host. Look into the entire estate. Get a trial of Sentry One Enterprise and use vSentry – and see what your host looks like.

High Performance (Everywhere….)

You need to have your windows OS running in HIGH PERFORMANCE, not balanced mode (I don’t understand why we still bump into that so much!) But not just that – you should have your BIOS set to that, and you should have your ESXi set to High Performance. Again – we’ve seen significant improvements from this alone.

Some closing thoughts….

There are many more considerations. The blessings and curses of snapshots. Other tricks with storage. HA/DR with a SQL Server technology vs. VMware HA. Keeping VM Tools updated and the hardware version updated. But just following the tips here – we’ve seen a 10-15% improvement from PVSCSI alone, 5-10% from Power Saving alone, 5-10% from right-sizing (lowering!) core counts or fixing Sockets: Cores – that’s a lot of performance optimization you can do right now without spending a dime on SQL Server consultants like us. (We’re happy to help. But we’re happy for you to handle these low hanging fruit items first yourself too!)

So. That’s a little that I wish you knew about VMware and SQL Server performance. VMware has some great materials too. I’ve linked some above. And this Best Practices white paper from them is a solid start, too.