The correct assignment of CPU resources is vital for CPU-intensive SQL Server workloads. Note the CPU capabilities of the AWS server hardware in your SDDC and the physical NUMA node configuration of your new servers, as they might differ from your on-premises physical servers.
- i3.metal: 36 physical cores, no Hyperthreading, Intel Xeon E5-2686 v4 (Broadwell), 2,3 GHz; 512 GB RAM; 2 pNUMA – 18 cores/256GB RAM each.
- i3en.metal:48 physical cores with Hyper-threading enabled (total of 96 logical cores), Intel Xeon Platinum 8200 series (Cascade Lake), 768GiB RAM, and approximately 45.84 TiB raw storage capacity per host. Two physical NUMA nodes, 24 cores/384 GB RAM each.
NOTE: The list above is subject to change once new instances are made available on VMware Cloud on AWS. Recheck the documentation for your SDDC for the server model in use.
Check the VM vNUMA configuration.
Recheck the cores/socket ratio configured for your VM
Rightsizing
Control the number of CPUs assigned to a VM hosting SQL Server workloads. You might need to downsize or add CPUs to your VM based on the difference between on-premises and VMware Cloud on AWS. The section in the document provides more information on the rightsizing process.
NOTE: At the time of writing, the CPU limit per VM on VMware Cloud on AWS is 36 CPUs on i3 and 96 CPUs on i3en instances based on the server hardware capabilities.
Check the VM vNUMA configuration.
Make yourself familiar with the NUMA concept and the VMware implementation of vNUMA. The vNUMA blog series by Frank Denneman is highly recommended. Also, check out his VMworld session: “60 Minutes of Non-Uniform Memory Architecture (HBI2278BE)” and information provided in the SQL Server on VMware document.
SQL Server is a NUMA-aware application capable of performance optimization based on the underlying NUMA configurations. VMware exposes the NUMA configuration with the help of vNUMA, the virtual topology configuration stored in the VM’s vmx file. By default, the vNUMA configuration for a VM is created only once – when a VM was first time powered on. During the VM lifecycle, vNUMA will be changed only when modifying a vCPU resource (using default settings) or other vNUMA advanced options. If the on-premises physical servers configuration differs from one used in VMware Cloud on AWS, it’s expected that after the migration, the vNUMA configuration of your VM may not be aligned with the new hardware. This might introduce performance penalties for SQL Server workload obvious on a VM with more than 8 vCPUs (9 is the lower limit for vNUMA).
To check the vNUMA configuration of your VM, we recommend using the PowerShell script (all credits go to Valentin Bondzio), providing a framework to execute grep against the vmware.log file without directly connecting to a host.
If the vNUMA configuration does not match, you can reconfigure the vNUMA by using either the VM advanced settings (by setting numa.autosize.once = "FALSE”) and powering on the VM or by changing the number of vCPUs assigned. You can find more information about SQL Server workload and vNUMA in the relevant sections of the SQL Server Recommendation Guide and the performance engineering team recommendations resource.
Recheck the cores/socket ratio configured for your VM
As per the best practices, always reflect the physical server CPU configuration while configuring the cores per socket assignment. The following examples might be helpful to illustrate the statement below
- For a VM with 24 In-Guest CPUs running on an i3.metal server, the recommended configuration will be 12 cores/2sockets (24 VM cores > 18 physical cores on one socket)
- For a VM with 24 In-Guest CPUs, running on an i3en.metal, the recommended configuration will be 24cores/1socket (24 VM cores == 24 physical cores on one socket)
On VMware Cloud on AWS, avoid configurations with more than two sockets – such configuration might negatively impact SQL Server performance by preventing effective use of the L3 processor cache.
CPU Hot Add
CPU hot-add feature, when enabled, disables the vNUMA configuration for a VM. Hence, if exposing vNUMA configuration is desirable, CPU hot-add should be disabled for all VMs hosting SQL Server workload with high-performance demand. The performance impact of CPU Hot Add is more visible on a VM having a wide vNUMA configuration.
Per VM EVC mode
Pre-VM EVC mode might need to be set before executing a live migration from on-premises to VMware Cloud on AWS or in case of a revert migration. If the EVC mode is configured, but the reverse migration is not expected, consider removing the EVC configuration to expose new CPU features to your VM (VM Compatibility upgrade might also be required)
