0

Looking for confirmation that we are overpaying for our VM :)

We have a SQL Server 2012 Standard Edition on a 7 socket (2 cores/socket) VM. From error log:

SQL Server detected 7 sockets with 2 cores per socket and 2 logical processors per socket, 14 total logical processors; using 8 logical processors based on SQL Server licensing.
Node configuration: node 0: CPU mask: 0x0000000000000003:0 Active CPU mask: 0x0000000000000003:0. This message provides a description of the NUMA configuration for this computer.
Node configuration: node 1: CPU mask: 0x000000000000000c:0 Active CPU mask: 0x000000000000000c:0. This message provides a description of the NUMA configuration for this computer.
Node configuration: node 2: CPU mask: 0x0000000000000030:0 Active CPU mask: 0x0000000000000030:0. This message provides a description of the NUMA configuration for this computer.
Node configuration: node 3: CPU mask: 0x00000000000000c0:0 Active CPU mask: 0x00000000000000c0:0. This message provides a description of the NUMA configuration for this computer.
Node configuration: node 4: CPU mask: 0x0000000000000300:0 Active CPU mask: 0x0000000000000000:0. This message provides a description of the NUMA configuration for this computer.
Node configuration: node 5: CPU mask: 0x0000000000000c00:0 Active CPU mask: 0x0000000000000000:0. This message provides a description of the NUMA configuration for this computer.
Node configuration: node 6: CPU mask: 0x0000000000003000:0 Active CPU mask: 0x0000000000000000:0. This message provides a description of the NUMA configuration for this computer.

As I understand it, Standard Edition supports the lesser of of 4 sockets or 24 cores, thus I conclude we are paying extra for 3 sockets/6 cores that are not being used (this is a dedicated SQL box).

I am thinking we have two choices:

  1. Update VM and remove the 3 unused sockets, so we have 4 sockets/8 cores.
  2. Update VM and remove the 3 unused sockets, update number of cores per socket from 2 to 4, so we have 16 processors. I need to check if this is even possible.

If #2 is possible, would this help with performance, assuming we are hitting CPU pressure at times?

  • 1
    Yes it does sound that the cores are not used 14 total logical processors; using 8 logical processors based on SQL Server licensing.. See this link about performance problems due to NUMA imbalance (it refers to Enterprise edition). – Kin Shah Aug 18 '16 at 18:50
1

I am thinking we have two choices:

  • Update VM and remove the 3 unused sockets, so we have 4 sockets/8 cores.

This is probably a better option. As Kin suggested in the comment on your question, NUMA imbalance would be a factor by trying to balance CPU affinity across physical cores.

  • Update VM and remove the 3 unused sockets, update number of cores per socket from 2 to 4, so we have 16 processors. I need to check if this is even possible.

Even if it is, go with fewer cores. On a virtual environment, this is preferred, especially with Standard Edition.

Final note: if you do share the host with other VMs, please be wary of something called CPU lag. This can happen when VMs with different numbers of vCPUs are running on the same host.

  • 1
    Yes, I just got word from our host provider that the recommended cores per socket is 1, thus adding more cores per socket could make things worse. – Greg Aug 18 '16 at 21:22

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.