A SQL Database Server is a core feature for most applications. In my experience a healthy database server can really speed up applications. In this article we take a closer look at how more CPU cores can help improving performance. And more important, how a wrong configuration can affect your wallet.

Adding extra CPU cores to your SQL Database Server gives it more horsepower. The more CPU’s or cores a computer has, the more things it can do at once, helping improve performance on most tasks.

How CPU’s are calculated

First of all your server needs to have access to enough CPU’s to handle the load. A normal PC has one socket with either Dual (2) or Quad (4) core. The number of CPU’s in a computer is calculated by multiplying the physical sockets with the number of cores placed within each socket. If virtualization is enabled this gets multiplied by 2. On my computer where I write this article I have 1 socket with a quad core and Hyperthreading enabled, giving me a total of 1 x 4 x 2 = 8 virtual CPU’s.

Nowadays, most servers are in fact hosted as Virtual Machines, where you can configure how many Sockets and Cores the VM uses. I suggest reading this cool article about vCPU configuration written by Frank Denneman (Chief Technologist at VMWare).

An Example

In the example below on a high-end Virtual Machine there are 16 sockets with 1 core each, 16 x 1 = 16 virtual CPU’s.

To verify how many CPU’s your SQL Database Server can see, type the following SQL Query:
select cpu_count from sys.dm_os_sys_info

To check how many CPU’s SQL Database Server is able to use, run the following SQL Query:

select scheduler_id,cpu_id, status, is_online from sys.dm_os_schedulers

So why are only 4 cores visible where I would expect it to be 16?

In fact there are CPU restrictions based on the chosen SQL Server license plan. The SQL Server offers a ‘Per Core’ licensing plan, which allows for a flexible license an unlimited amount of users hosted in both Physical as Virtual environments (VMWare, Microsoft Hyper-V, and many more).

The max compute capacity in a standard license plan restricts the number of Cores and Sockets: Lesser of 4 sockets or 16 cores (version 2012, 2014) and Lesser of 4 sockets or 24 cores (for version 2017).

You can read more about Compute capacity limits by edition of SQL Server in this article.

Conclusion

The trick is to choose a SQL license plan closest to the hosting machine. If we host a Standard Edition on the mentioned High-end server with 16 sockets (1 core per socket) and 16 virtual CPU’s, the SQL Server engine is only able to use a maximum of 4 CPU’s at the same time. The other 12 CPU’s will end up basicly idle.

Or in other words: every core is linked to 1 socket. So I have 16 cpu’s, but the ‘lesser of 4 sockets’ limits this to 4 effective cpu’s.

One way of having the Standard Edition of SQL Server with the maximum number of CPU’s is having 4 sockets configured with either 4 Cores each (version 2012/2014) or 6 Cores each (version 2017).

Having a database server with more CPU power than the license allows can be quit costly. In a Virtualized environment the cost for a VM are often calculated as a per core.

Another option would be to use SQL server Enterprise edition which allows the max number of Cores the underlying OS supports.

Have you checked your SQL Server core usage today? Let us know in the comments below!