SQL Server does not use all assigned CPUs on VM

As Virtual Machines (VMs) are getting more powerful, we have been migrating many SQL Servers into VMs and recently after we did a initial requirement for our Veeam Backup DB server, we started to notice performance issues.  On the VM, only half of the CPUs were 100% while the others were less than 20%. We checked to make sure there we have enough CPUs assigned in total 16 and even tried adjusting to make sure there was no MAXDOP hints on the queries.  Even after checking these, the VM still only used the first 8 CPUs out of the 16 CPUs.

Solution

After some research, we found that the issue was due to a combination of the SQL Server edition (Standard Edition that we were using) and the VM configuration.

When using a VM with SQL Server standard edition, there are some limits for how CPUs are utilized.  On the Compute Capacity Limits by Edition of SQL Server page, if you have standard edition, the VM is limited to the lesser of 4 sockets or 24 cores, as shown below.

Check Available CPUs with a SQL Server Query

We can use this query to check how many CPUs SQL Server sees and is using.

SELECT scheduler_id, cpu_id, status, is_online FROM sys.dm_os_schedulers 
GO

If your SQL Server has 16 CPUs, but only 8 show “VISIBLE ONLINE”, this means you only can use a maximum of 8 CPUs no matter what.

Check Available CPUs from VM Virtual Hardware part in VSphere

Looking into this, we noticed that our VM build had “Cores per Socket” equal to the number of total CPUs (2 in our case) and had “Sockets” equal to 8.  This equates to 16 CPU cores, which is what we wanted, but this violated the limit of 4 sockets, so SQL Server standard edition only recognized 8 cores.

This is what correct values need to look like below:

Cores per socket 4 and Socket 4 which also equates to 16 CPU cores but this time all are recognized by SQL server and can be utilized and we resolved this CPU issue.