Sensational
CPUs aren’t normally expensive, but ask them to run a query and the cost skyrockets. It doesn’t matter where you are in the world or in the cloud, prepare to get gouged.
You’d think SQL Server was an Hermes purse.
Life is a bit worse in the cloud, where CPU count and RAM amount are inexorably tied together. Sure, Azure offers constrained vCPU instances that help with that, but still.
Money is expensive, and it never goes on sale.
Slacker
If your CPU load stays consistently under 20%, and you don’t need a bunch of cushion for regularly scheduled tasks like ETL or ELT or LET me write a sentence before changing the acronym, then what’s the point of all those CPUs?
I’m not saying they have to run at 100% and you should be using them to air fry your Hot Pockets, but what’s wrong with running at 40-60%? That still leaves you a good bit of free ticks and cycles in case a parameter gets poorly sniffed or some other developer disaster befalls your server.
When a workload is pretty well-tuned, you should focus on right-sizing hardware rather than staring at your now-oversized hardware.
Bragging Rights
It’s often quite an achievement to say that you tuned this and that and got CPU down from 80% to 20%, but now what?
Can you give back some cores to the VM host? Consolidate workloads? Move to a smaller instance size?
Fashion models are great, but they’re not great role models for servers. CPUs should not be sitting around being expensive and bored.
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.
The only caveat that I’d add, here, is to not count HT cores in the cpu usage percentage. Yes, they show up as a full core on Windows and Linux, however in neither architecture are they themselves full execution units, so it would be folly to expect the performance of a full core from it. So if you’re using HT on your server and you’re hitting 50% cpu usage then there is a good chance you’re not going to scale a whole lot further.
Good point! When would you tell someone to turn off HT?
Good question!
If you’re using cloud, then I guess that’s a moot point. Most cloud offerings you don’t have a choice unless there is a special all full cores tier. I’m sure that’s pricey… as in you could buy 3x servers at the same cost each year and get better latency and perf…
If you’re using your own hardware then I would test a typical workload against the server with HT turned off and then turned on. Sometimes, depending on the workload pattern, you can get better performance by turning it off. However, I’d say in most cases that I’ve seen HT turned on generally doesn’t hurt, it just doesn’t give you 2x the capacity. Assume that you’ll, maybe at best get 30% more than with it turned off.
There isn’t a hard and fast rule, though it does seem the more dense the chip, the less helpful it tends to be. That’s just anecdotally as I have no giant data pool for that – but with 20-24 phsyical core and higher processors becoming normal that really starts to create issues in other parts of the architectures such as memory interconnects when HT gives you 48 cores to start asking for data. There is also a limit of 64-cores in a windows k-group and wonky things start happening above that number of logical cores per chip. Anecdotally, I’ve witnesses NUMA not really scale to larger cores per node – somewhere around 12 seems to be about the maximum for performance as it seems NUMA doesn’t really scale well.
Thanks, yeah, I’ve often wondered how the super high core count single socket procs would play with all this stuff.
NUMA, in fact, is not very scalable that many manufacturers that sell high density core server have options called sub-numa which create numa domains inside a numa node, for example, HP does this: https://techlibrary.hpe.com/docs/iss/proliant-gen10-uefi/GUID-0200DE9A-AFCF-4CF0-919D-713E2A8B915A.html
My personal opinion is to stay away from huge monolithic servers using hundreds of processors and use the divide and conquer approach. That doesn’t mean using 2 core servers all over the place, use good judgement but stop loading up servers with hundreds of databases that are doing olap + oltp + htp + whatever new flavor of the month.
BUT THERE ARE INTEGRATIONS SEAN AND THEY NEED TO BE TOGETHER