SQL Server is using only one processor/CPU

Recently I have collaborated in some cases with the same symptom: customer reported that is using SQL Server and noticed that in the machine only one CPU hit 100% utilization, while the utilization of the other CPUs didn´t have a sustained usage of more than 10%.

The scenarios have the same characteristic: using a SQL Server Express in a virtualized environment.

DBAs should be thinking about affinity mask and hard binding behavior, but this is not the case here, and the explanation is much simpler than that: product limit.

If you check the documentation, for instance, Features Supported by the Editions of SQL Server 2014 (https://msdn.microsoft.com/en-us/library/cc645993(v=SQL.120).aspx), you will notice:

Maximum Compute Capacity Used by a Single Instance (SQL Server Database Engine) -> Express -> Limited to lesser of 1 Socket or 4 cores

SQL Server express support up to 4 cores in the SAME socket, so the virtual machine definition matters a lot.

In the following image you can see that the virtual machine is being defined by having 12 sockets with a single core per socket, so a SQL Server Express in this machine would only use one core.

This is really common for deployments that use SQL Server Express, like Skype for business (former Lync), that being smaller VMs, sometimes we don´t give proper attention to its configuration, as we would for a VM that would host SQL Server Enterprise.

A similar scenario can also happens for SQL Server Standard, but in this case using only 4 processors in a 16 socket virtual machine (Limited to lesser of 4 Sockets or 16 cores).

To avoid performance issues related to NUMA / vNUMA definition, our recommendation is to have the same virtual layout as the one defined in your physical processor architecture.

This scenario should be really straightforward to DBAs, but sometimes the organization doesn´t have a DBA involved when using SQL Server express, so hopefully this post can help someone.

Luciano Caixeta Moreira -  [ Luti ]
luticm79@hotmail.com https://www.linkedin.com/in/luticm www.twitter.com/luticm