Avoid CPU core mismatches

Choosing a server that has a higher number of cores than your license covers can cause CPU skew and wasted CPU power. This is because of the mapping between logical and actual cores. When you use SQL Server with a Client Access License (CAL), some schedulers will be VISIBLE ONLINE and the rest will be VISIBLE OFFLINE. This can lead to performance issues with non-uniform memory access (NUMA) topologies, because of scheduler nodes not being utilized optimally.

For example, if you run SQL Server on an m5.24xlarge instance, it will detect two sockets with 24 cores, and 48 logical processors per socket, which results in a total of 96 logical processors. If you have a license for only 48 cores, you would see a message similar to the following in the SQL Server error log:

2020-06-08 12:35:27.37 Server SQL Server detected 2 sockets with 24 cores per socket and 48 logical processors per socket, 96 total logical processors; using 48 logical processors based on SQL Server licensing. This is an informational message; no user action is required.

If you see a difference between the total cores and the number of cores being used by SQL Server, check for CPU usage imbalance or use a server type that has the same number of cores that your license supports.

CPU skew: For the instance type in our example (m5.24xlarge), SQL Server creates eight NUMA nodes by default. Only four of these nodes (parent node ID 0,1,2,3) have schedulers with the status VISIBLE ONLINE. The remaining schedules are all VISIBLE OFFLINE. This disparity among schedulers can lead to performance degradation.

To check the scheduler information and status, use:

 $ select * from sys.dm_os_schedulers 

If you want to use a server instance that has a higher number of cores than your SQL Server license supports, consider customizing the number of cores by following the instructions in Specifying CPU options for your instance in the Amazon EC2 documentation.