Identifying Performance Problems With SQL Server Virtual Machines

Errywhere

---

Everyone’s on VMs these days. That’s cool. Nothing against computers.

But here’s how people screw up SQL Server on VMs bigtime:

• Fewer than 4 cores (this is a licensing minimum)
• Multiple single-core sockets
• Not fully reserving memory
• Oversubscribing hosts

Why are these bad?

• 4 cores is the minimum license
• Single CPU sockets often leave Standard Edition with unusable CPUs
• Memory gets siphoned off to other processes
• CPU intensive workloads can’t get a thread in edge-wise

Pox

---

This doesn’t even touch more advanced concepts, like CPU ready time, NUMA alignment, power modes, or the impact of the hot add option.

Even when you get all that right, you’re left with storage waits that make trans-Pacific flights look tolerable.

I often find myself pointing people to this article by Jonathan Kehayias: Troubleshooting CPU Performance on VMware.

Also, the official guide for SQL Server from VMware. They update the guide fairly often, and I’m linking to one from April of 2019.

Make sure you’re looking at the latest and greatest.

Cloudness

---

The Cloud is basically an AirBnB for your server. Again, that’s cool.

They’re still VMs.

But the point is: pay close attention to how cloud VMs are set up for SQL server.

They’re not doing any of the stuff I listed up there.

Sure, storage and networking still kinda sucks, even if you pay for the good stuff.

But no one puts out the nice linens for strangers.

The point here is that they want you to complain as little as possible for the price.

Part of that is not goofing up the obvious stuff.

Thanks for reading!

Going Further

---

If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 75% discount to my blog readers if you click from here. I’m also available for consulting if you just don’t have time for that and need to solve performance problems quickly.