Allocating CPUs to a Virtual Machine (VM) running SQL Server Standard on a VMware host should be pretty easy right? Yes and No. There are a few constraints when making CPU allocation changes to the VM, otherwise SQL Server may not be able to take advantage of the additional computing power. SQL Server Enterprise is limited by the Operating System Maximum and is not subject to these constraints. Refer to the table below for compute maximums of SQL Server Standard:
|
SQL Server Standard Version
|
Maximum Compute Capacity |
| 2014 | Limited to the lessor of 4 sockets and 16 cores |
| 2016 | Limited to the lessor of 4 sockets and 24 cores |
| 2017 | Limited to the lessor of 4 sockets and 24 cores |
When you change the number of CPUs allocated to a VM on VMWare you must make sure not to exceed these maximums. When you edit the properties of the VM, determine the total number of CPUs you want to allocate to the VM. After you allocate the number of CPUs then enter a value for the cores per socket – this will determine the total number of CPU sockets that the VM will see. By default, VMware allocates one core per socket, but you change this value. Consider the example below:
In this example a total of 16 CPUs was allocated to the VM with 4 cores for each CPU. The number of sockets that the VM will see is 4 (16 CPUs/4 cores). Here's another example:
In this example a total of 16 CPUs was allocated to the VM with 16 cores for each CPU. The number of sockets that the VM will see is 1 (16 CPUs/16 cores). Here are a few general rules when allocating CPUs to a VM running SQL Server Standard.
- Do not exceed 4 sockets.
- Do not exceed 24 cores for SQL Server 2016/2017 or 16 cores for SQL Server 2014.
- Having fewer sockets with more cores tends to work better with SQL Server.
- Minimize the number of different VM CPU configurations on a VMWare host. Having a single host with many different VM CPU configurations makes the scheduler work much harder and will slow down performance of all VMs running on the host.
- Never exceed the number of physical cores on the SQL Server Host.
- Never exceed the amount of physical RAM on the host.
- Non-Uniform Memory Access (NUMA). Starting with vSphere 6.5 vNUMA access is automatically optimized. However, you must consider the total number of cores running on each socket on the host and the amount of memory installed on the host. If you have a two-socket host with 20 cores (40 cores total) and 1TB of memory (512GB of RAM per CPU) you should verify that your VM is optimized for the environment. In this example you can have a VM that has up to 20 Cores and 512GB of RAM configured with a single socket. If you exceed 512GB of RAM on the VM and the VM is configured with a single socket, then the vNUMA configuration will be non-optimal because it will have to access memory from the other CPU. In this example if you need to allocate more than 512 GB of RAM to the VM, then configure the VM with two sockets that the load will be spread over both CPUs. Of course, in this example you're still "limited" to 1TB of RAM and 40 cores to the VM.
- If you're licensing SQL Server Standard by Core make sure you have enough licenses to cover the CPUs allocated to the VM. Each SQL Server Core license entitles you to two cores.
CPU allocation with SQL Server Standard can be very tricky when you're attempting to scale a VM over 4 sockets. In order for SQL Server Standard to take advantage of the compute power, make sure you don't exceed 4 sockets and a total of 24 cores.