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.
11 thoughts on “VMware and SQL Server Best Practices.”
Great article. Typo at the end:
that’s a lot of perfromance
Hi, great article. We’ve recently made some adjustments with the SCSI controllers. All our disks using the one controller! Massive improvements when we added more adapters! You mentioned vSentry, where can I find this? Cannot seem to find it in the SentryOne website.
That’s fantastic. Feels good to make such a difference with so little effort 🙂
The ability to monitor VMs is just included in Sentry One. I believe if you have the enterprise license you have it included already. Sentry One is our default go to monitoring tool here with all of our clients also.
I wish you knew how to write VMware.
I moderate comments to prevent spam – but not anonymous comments that are trying to be helpful or even point out an oops. And thank you! I’ve been working with SQL Servers on VMware for quite some time – always as the SQL guy – and somehow I’ve been managing to spell VMware with like “VMWare” all along. Not sure if you were trying to be helpful and being tongue-in-cheek with the title or if you were having a bad day. You didn’t leave an e-mail so I can’t reach out and say thank you directly, so thanks! I genuinely mean it! That’s embarrassing. I’ve been typing it wrong all this time. Here’s hoping the habit changes from here on out.
Hey, I capitalize the wrong letters all the time. I’ve gotten too many code reviews correcting my capitalization to count.
Don’t let Anonymous keep you down. 😀
Ha! Cheers Anthony!
Nah – I’m actually relieved someone told me! I don’t want advice that could help someone get the best performance possible out of their SQL Servers in a virtualized world get ignored because I made a sloppy oops. I have a few words I always pause when typing. For some reason, my mind just always wants to screw up “tomorrow”, “fulfillment”, and “parallel” – something about the double letter thing – As a much younger man I always wrote “tommorow” (had to type that three times – Chrome or Grammarly wouldn’t let me type it wrong. Neither one of them bothered to help me get “VMware” right, though..) I love a chance to say “OOPS!” and “Thanks!” Try and fix something about me every day (well, Ideally a LOT more than one thing – I have a lot to fix!) so mistyping something is an easy one! 🙂
Very handy! Thanks for the tips