0

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 :

http://imgur.com/a/1t8oQ

  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

  • Do your SQL Servers have any CPU or NUMA affinity settings enabled? Do you have MAXDOP configured and, if so, how? Is the Windows OS running the Balanced or High-Performance power plan (affects CPU p-state)? – SQL_Underworld Jan 19 '17 at 6:09
  • FIX - we disabled hot-add CPU and SQL NUMA config was corrected. It was seeing 1 numa node with 16 cores earlier, and now it sees 2 with 8 each. – d-_-b Jan 19 '17 at 15:26
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.

  • "With Hyperthreading enabled, then you're only using the cores on one socket" - you mean I could actually get 32 cores (since it's 2 socket x 8 cores each x 2 HT) from this 'dedicated' host, but only getting 16 ? – d-_-b Nov 3 '16 at 5:56
  • 1
    You would need to check how the instance is configured, but if you're only getting 16 vCores presented to the OS/SQL Server on one NUMA node, and Hyper-Threading is enabled, then you're only using one socket on the host. Accessing a 2nd physical socket will cause another vNUMA node to be created and appear in SQL Server. I didn't edit my response as clearly as I thought I did. My apologies. – Stephen Falken Nov 3 '16 at 6:21
  • host & guest info - imgur.com/a/z1bQc . the host has HT enabled and says 32 logical cores available, and looks like I could go up to 32 cores on the guest VM, up from current 16. but would it make a performance difference ? If you can confirm this, it means we have been running on half the processing power all this while. – d-_-b Nov 3 '16 at 6:27
  • It will make a performance difference. SQL Server will have a greater number of schedulers, which means more workers, which means more threads can be executed across more cores - you'll still be constrained by RAM and storage I/O though. Unless you establish a baseline (I like to use HammerDB using the TPC-C standard and capture performance data using PerfMon), and then run the same thing after you add the cores and restart, you won't be able to quantify it. – Stephen Falken Nov 3 '16 at 7:03
  • our sysadmin states that it won't make a difference because the host abstracts hardware and only presents free cores, whether HT or not. I then found this article that says – don’t assign more than actual physical cores, regardless of logical cores - vmwarebits.com/content/… .. so i'm a bit confused – d-_-b Nov 3 '16 at 16:06

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.