How many Cores per Socket do I need?

, 2018-12-11

(Be sure to checkout the FREE SQLpassion Performance Tuning Training Plan - you get a weekly email packed with all the essential knowledge you need to know about performance tuning on SQL Server.)

Today I want to talk about a very interesting topic in VMware vSphere: the Cores per Socket option, and how you should configure it for your SQL Server based Virtual Machine.

The VMware Recommendation

The recommendation from VMware is that you set the Cores per Socket option to 1. This means that you are getting for each vCPU that you have assigned to your Virtual Machine 1 virtual Socket (vSocket) within your Virtual Machine. It is recommended by VMware, because with this setting, ESXi also represents the correct NUMA topology to your Virtual Machine.

As you know from my Home Lab deployment, I have in each physical ESXi Host 2 physical CPU Sockets, each with 8 physical Cores. If I create a Virtual Machine with 16 vCPUs, and I set Cores per Socket to 1, I get within that Virtual Machine 16 vSockets and 2 NUMA Nodes.

2 NUMA Nodes are available within Windows

Therefore SQL Server can perform its NUMA optimizations and everything is fine. Is everything fine?

A good recommendation?

No, nothing is fine with that recommendation, because the Virtual Machine has now 16 vSockets. And that is a huge problem for SQL Server – in the Standard Edition. The Standard Edition has some limitations on how many CPU resources you can use:

  • 24 CPU Cores
  • 4 CPU Sockets

As you can see, the Standard Edition is restricted to only 4 CPU Sockets. In my case I have a Virtual Machine with 16 vSockets – much more. And how many CPU Cores is SQL Server using? Only 4, because we have in each Socket only 1 Core available:

SQL Server Standard Edition is restricted to only 4 CPU Sockets in Standard Edition

In that case, SQL Server has now only 4 Schedulers in the state VISIBLE_ONLINE, and all the others are just VISIBLE_OFFLINE. But you can work around that problem by reconfiguring the Cores per Socket option of your Virtual Machine. Let’s give our Virtual Machine 8 Cores per Socket. This gives us now only 2 vSockets in the Virtual Machine. Let’s check the SQL Server Log again:

Now we have the full power of SQL Server Standard Edition

As you can see now, SQL Server is now able to use all 16 vCPUs, because they are only spread across 2 vSockets. And SQL Server still sees the correct NUMA topology consisting of 2 NUMA Nodes.

Caution: Please also check out this blog posting from VMware, where they describe the vNUMA changes implemented in ESXi 6.5+. In earlier versions of ESXi (<= 6.0), you could also have a wrong vNUMA topology, if you set a custom Cores per Socket value!!!

https://blogs.vmware.com/performance/2017/03/virtual-machine-vcpu-and-vnuma-rightsizing-rules-of-thumb.html

Summary

Recommendations are just recommendations. Nothing more. VMware made the recommendation to set the Cores per Socket to 1, so that the correct vNUMA topology can be presented to your Virtual Machine. But in combination with SQL Server Standard Edition, you can get quite fast into serious troubles, because SQL Server is not able to use all vCPUs!

If you are more interested in how to Design, Deploy, and Optimize SQL Server on VMware vSphere, you can also check-out my Online Training about it.

Thanks for your time,

-Klaus

Rate

Share

Share

Rate

Related content

Database Mirroring FAQ: Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup?

Question: Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup? This question was sent to me via email. My reply follows. Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup? Databases to be mirrored are currently running on 2005 SQL instances but will be upgraded to 2008 SQL in the near future.

2009-02-23

1,567 reads

Networking - Part 4

You may want to read Part 1 , Part 2 , and Part 3 before continuing. This time around I'd like to talk about social networking. We'll start with social networking. Facebook, MySpace, and Twitter are all good examples of using technology to let...

2009-02-17

1,530 reads

Speaking at Community Events - More Thoughts

Last week I posted Speaking at Community Events - Time to Raise the Bar?, a first cut at talking about to what degree we should require experience for speakers at events like SQLSaturday as well as when it might be appropriate to add additional focus/limitations on the presentations that are accepted. I've got a few more thoughts on the topic this week, and I look forward to your comments.

2009-02-13

360 reads