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

Question

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

sqld-_-ba

SSCarpal Tunnel

Points: 4365
More actions
November 2, 2016 at 6:37 pm

#337086

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 :
1. SQL Server Processor NUMA config is attached - 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 is attached - 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

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Perry Whittle SSC Guru Points: 234013 More actions · Answer 1

sqld-_-ba (11/2/2016)
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.

If this is the case, it possibly shouldn't be a VM.

All you're doing is wasting the cost of a VMware ESX licence

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" 😉

frederico_fonseca SSCoach Points: 16167 More actions · Answer 2

have a look at this document http://www.vmware.com/content/dam/digitalmarketing/vmware/en/pdf/solutions/sql-server-on-vmware-best-practices-guide.pdf.

As said above if a physical host is going to be fully dedicated to a single SQL instance then its better to have it without VMware.

And make sure that in any case the machine bios (if applicable), vmware and windows are set to high performance on its power settings

As you said money isn't an issue I would pick a I7 instead of an I5 and throw at it lots of the fastest RAM you can get.

E7-8891-V4 10 cores(20HT) 2.8GHz 60MB cache 102GB/s memory

or eventually a I5 but not the one you mention

E5-2687W-V4 12 cores (24HT) 3.0GHz 30MB Smart cach 76.8GB/s memory

Note that the E54627-V4 does not have HT

Either way big cost is on SQL Licenses

and yes HT should be enabled - very few highly intensive computing software where that may be better - SQL isn't one of them.

sqld-_-ba SSCarpal Tunnel Points: 4365 More actions · Answer 3

Virtualization offers us other benefits like snapshots etc., and we're OK with the slight overhead.

Only now it is dedicated, cos VM needs that many cores. After we upgrade, it can potentially host other VMs

Looks like the E7 UCS blades are 3 times as much ($15K) as the E5 blades ($5K). I said money is not an issue but I don't hold the purse strings 🙂

Shall bring it up to system team.