SQL Server CPU limitation for Express and Standard Edition

Introduction

When performing an audit on an SQL Server VM the first thing the system administrator told me was that there’s no issue with the CPU on this box, the monitoring shows it’s always around 50% usage.

Problem

Here is the Task Manager:

The average CPU usage on this VM is indeed 50% but half of the cores are stuck at 100%.
There are two SQL Server instances installed on this server but they are both Express Edition.

Compute capacity limits by Edition

As stated in the documentation the compute capacity limit for SQL Server Express Edition is the lesser of 1 socket or 4 logical cores.

So what happens is that the VM is configured with 2 sockets and 4 cores. Both instances are limited to 1 socket and therefore to 2 cores.
One way to confirm this is to look at the sys.dm_os_sys_info DMV.
We can see that the scheduler_count is 2. Each scheduler is mapped to an individual processor (CPU) as described in the Thread and Task Architecture Guide.

The same information can also be retrieved from the Error Log with the following query.

EXEC sys.xp_readerrorlog 0, 1, N'detected', N'socket';

Solution

To improve this situation a solution without impacting the availability of the service would be to set a Processor Affinity mask as it does not requires a restart of the service.
Each instance could be configured to use 2 differents cores so the workload would be balanced over the 4 cores.
I would not recommend doing this as the result is still sub-optimal.
The best option here is to re-configure the VM with a single socket so both instances could use 4 cores.
This is what we have done for this VM. Now the instances can use 4 schedulers and the load

Conclusion

There are some CPU limitations with non-Enterprise Editions of SQL Server.
Make sure you are aware of them and configure your VMs accordingly or you may face unexpected CPU contention on your instances.

PS:

Just before publishing this post, I identified the same issue on another VM. This one has several SQL Server instances installed with Standard Edition.
The Standard Edition is limited to the lesser of 4 sockets or 28 cores. The VM is configured with 8 cores but there are also 8 sockets. So only 4 cores can be used by SQL Server.
Reconfiguring the VM with a maximum of 4 sockets will again allow the use of all cores.