1

I have been trying to find out what the best VM configuration would be for our SQL Server 2012 on VMware 5.1 would be.

The VM host(s) has 2 sockets with 4 cores running hyper threading (total of 16 CPUS), we have a total 48 GB Memory on the host(s).

Our SQL Server VM has 8 vCpu's and 32 GB Memory allocated, when looking at the CPU configuration within SQL it is reported that there is only one NUMA node. My concern is that the 32 GB Memory allocation crosses the NUMA boundary and from what I understand this will not give us our best performance.

How best can we configure our SQL VM to best use the NUMA configuration on the Host? One article suggested adding more vCpu's to the VM, another configuring some advanced setting on the VM, and another reducing the amount of Memory allocated to 50% of the host. (24 GB)

Any advice would be greatly appreciated.

Robert Brown
  • 125
  • 1
  • 4

2 Answers2

2

You're correct that you typically want to align your guest so it either fits within a single NUMA node (narrow). If you do go wide in your current scenario (by memory or cpu), I agree that you're getting into a one-vm per host configuration.

It's hard to know whether narrow or wide is a better fit for the VM in question without knowing an awful lot about the SQL Server and its bottlenecks. But it is generally true that memory is very powerful at alleviating IO pressure for SQL Servers and is very often helpful -- so I think your long term plan of increasing the amount of memory to be able to keep the guest narrow and give it more memory is sound.

With your version of vSphere you do have the option to make your virtual machine NUMA aware, but it's a very specific configuration setting. It isn't done by simply setting the socket/processor ratio on the VM.

The advanced setting value you are looking for is "numa.vcpu.maxPerVirtualNode", on your server you have two physical sockets each with 4 hyper-threaded cores so set this value to 4. That will cause VMware to allocate 4 Virtual CPU's on each socket.

It's enabled by default for guests with more than 8 vCPUs, so it wouldn't be on by default for yours. You will want to keep all the hosts configured the same way, as migrating to hosts with different NUMA configurations could be bad news.

More info:

Robert Brown
  • 125
  • 1
  • 4
Kendra Little
  • 400
  • 2
  • 5
1

I think you've over-configured your VM for the host hardware you have now. I try not to cross NUMA boundaries on hosts like this... So your 8 vCPU count is okay, but RAM needs to go down to 24GB or less. I would probably start with fewer vCPUs, unless you know that you need 8... and if you really need 8 vCPU, the hardware should be bigger.

A nice breakdown of this is here.

Also see: HP DL360p with Intel E5-2630 NUMA Capable?

What other systems will be running in this environment? How's your cluster capacity? How many hosts will you have?

Community
  • 1
ewwhite
  • 194,921
  • 91
  • 434
  • 799
  • We have a 5 Blade Host Environment, all configured the same & vmotion is enabled. Currently there are two other smaller VMs running on the same host. My problem is this SQL server is under constant load and has an average of 60% CPU utilization our free memory is also very low. I am worried that reducing the memory may not leave sufficient memory for SQL. In about 2 months time we are planning on adding more Memory to hosts this will address the memory Numa boundary problem, i am looking for an interim solution. – Robert Brown Aug 15 '13 at 11:28
  • Given a database of sufficient size, your memory usage will *always* be high. SQL Server is memory hungry. What is your PLE like at your current memory allocation? – Ben Thul Aug 15 '13 at 12:16
  • @RobertBrown You can do it (increase RAM), but it's not optimal... but if you have enough failover capacity and no memory pressure in the cluster, go for it. So as an interim solution, it's quite fine. – ewwhite Aug 15 '13 at 12:44
  • Our PLE is currently sitting at 15210, although it is the end of the day and the system is quiet. Will have to check again during out business hours. What I did try on our test environments was to configure VMware to give the Server 2 Sockets, 4 Cores with a maximum of 4 Cores pre NUMA node. According to the VMWare documentation this will force exsi to supply 2 NUMA nodes and the memory split 50:50 across both NUMA nodes. That way SQL can see the NUMA nodes and allocate memory accordingly. I Checked in the OS and 2 Nodes were allocated with 4 CPUs per node. – Robert Brown Aug 15 '13 at 16:29