SQL Server Virtualization best practices - NUMA, HT, Cores vs Speed

Question

We run on Cisco UCS + VMware ESX + HP 3PAR.

Host blade config : UCSB-B200-M4 , Xeon E5-2667 v3 3.1GHz , 2 sockets , 8 cores each, Hyper-Threading Active So total 16 physical cores, or 32 logical cores.

We have Software Assurance with M$, so all are Enterprise editions, and lot of additional SQL core licenses paid for, so money is not an issue.

Our primary single OLTP SQL VM is 'dedicated' to one of the hosts, i.e. no other VMs are allowed to run on it, cos it requires all 16 cores of power. Even with that, CPU regularly runs ~60-80% , so we're planning to upgrade hardware. Questions below :

https://i.sstatic.net/we036.jpg

1. SQL Server Processor NUMA config - all 16 processors are on 1 NUMA node - But I thought since the host has 2 sockets, there would be 2 NUMA nodes ?

2. vCenter Socket/Core config - 16 sockets with 1 core per socket - Is it better to change this to 2 sockets with 8 cores per socket since that matched underlying hardware ?

3. I recently heard at PASS conference, that if an entire VM is being dedicated to a single host, then leave Hyper-Threading disabled. Anybody agree/disagree ?

4. As a basic pen-paper calculation, we need to double our 'compute capacity' from 50 (16 cores x 3.2Ghz) to ~100 GHz.
   Is it better to go for higher core-count across 2 sockets:
   E5-4669 : 2 socket x 22 cores x 2.2 Ghz = 96.8 Ghz
   or higher clock speed across 4 sockets:
   E5-4627 : 4 socket x 10 cores x 2.6 Ghz = 104 Ghz

Answer 1

The best thing you could do is read the VMWare guide "SQL Server on VMWare Best Practices Guide". This holds all the information you need. However...

1. Unless the instance is configured as a "wide VM", taking vCores from each socket, then you won't see 2 NUMA nodes. With Hyperthreading enabled, then you're only using the cores on one socket. (3.3.4 NUMA Consideration).

2. From the best practices guide: "VMware recommends setting the number of cores per socket to one when allocating virtual CPUs to VMs on the vSphere platform. That means that a VM that requires 8 CPUs will have 8 vCPUs with 1 core each." (3.3.5 Cores per Socket). Test this yourself though.

3. Where I'm working, the answer is "it depends". Some instances we leave it enabled, and some we don't. However... "VMware recommends enabling Hyper-threading in the BIOS so that ESXi can take advantage of this technology." (3.3.3 Hyper-threading)

4. If there are going to be no other instances on the host, then it's not as big a factor as if there's going to be more than one instance. It would be of benefit to have those allocated to separate sockets if you're looking to maximise performance. Unfortunately, you'd need to do quite a lot of testing and data capture to make an objective determination - I haven't found anything from VMWare specifically relating to this.

Go through the entire guide and read it: http://www.vmware.com/content/dam/digitalmarketing/vmware/en/pdf/solutions/sql-server-on-vmware-best-practices-guide.pdf.

Other considerations are checking power schemes, because these can throttle CPUs so low that you get very poor performance, even from the best CPUs you can afford.

Answer 2

From the best practices guide:

VMware recommends setting the number of cores per socket to one when allocating virtual CPUs to VMs on the vSphere platform. That means that a VM that requires 8 CPUs will have 8 vCPUs with 1 core each.

That seems like bad advice (doesn't seem to be in the current guide) as as setting 1 core for 8 CPU's would mean that SQL Standard 2016 would only utilize 4 due licensing and would remove the usefulness of MaxDOP.