SQL Server not using all cores

Question

I have a feeling that my SQL 2016 Standard server is not using all of the cores available.

The virtual machine is set up as

1 core per virtual socket
16 virtual sockets
for a total of 16 virtual cores.

Running this on the SQL Server

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

I get

0 0 VISIBLE ONLINE 1
1 1 VISIBLE ONLINE 1
2 2 VISIBLE ONLINE 1
3 3 VISIBLE ONLINE 1
4 4 VISIBLE OFFLINE 0
5 5 VISIBLE OFFLINE 0
6 6 VISIBLE OFFLINE 0
7 7 VISIBLE OFFLINE 0
1048578 0 HIDDEN ONLINE 1
8 8 VISIBLE OFFLINE 0
9 9 VISIBLE OFFLINE 0
10 10 VISIBLE OFFLINE 0
11 11 VISIBLE OFFLINE 0
12 12 VISIBLE OFFLINE 0
13 13 VISIBLE OFFLINE 0
14 14 VISIBLE OFFLINE 0
15 15 VISIBLE OFFLINE 0
1048579 8 HIDDEN ONLINE 1
1048576 0 VISIBLE ONLINE (DAC) 1
1048580 1 HIDDEN ONLINE 1
1048581 2 HIDDEN ONLINE 1
1048582 3 HIDDEN ONLINE 1
1048583 4 HIDDEN ONLINE 1
1048584 5 HIDDEN ONLINE 1
1048585 6 HIDDEN ONLINE 1
1048586 7 HIDDEN ONLINE 1

Correct me if I am wrong but this shows that I am only using 4 cores? is that correct? Can anyone tell me why?

Answer 1

Your SQL licensing might only be for a certain amount of sockets which would make it only work on that set amount. I'd check your NUMA and adjust it where you use less sockets, more cores. Socket represents the physical core so you have the server trying to use 16 physical cores. Try using 4 sockets with 16 virtual cores (4 cores per socket).

https://download.microsoft.com/download/9/C/6/9C6EB70A-8D52-48F4-9F04-08970411B7A3/SQL_Server_2016_Licensing_Guide_EN_US.pdf

Answer 2

Can you post the first 100 lines of sp_readerrorlog. Also please add the output of select @@version in the question. You have standard edition and I believe the setting of cores is such that you are not able to see all cores. Plus note SQL Server standard has limitation to lesser of 4 sockets or 24 cores.

See This cpu-cores-or-memory-offline-the-problem-with-standard-edition-vms. Output from errorlog will clear everything.

EDIT: From your question

1 core per virtual socket,16 virtual sockets, for a total of 16 virtual cores.

In this case since you have 1 core per socket and as per Standard edition limit it will use lesser of 4 sockets or 24 cores. you have SQL Server showing and using only 4 processor online.

Answer 3

Please see:

https://www.brentozar.com/archive/2018/06/cpu-cores-or-memory-offline-the-problem-with-standard-edition-vms/

Answer 4

Hi @John D ，

It is limited to the lesser of 4 sockets or 24 cores when using SQL Server standard edition.
If your VM has 16 sockets, but only 4 show "VISIBLE ONLINE", this means you only can use a maximum of 16 sockets no matter what.
As DanielReiche-5949 mentioned, to assign 16 cores to the VM, you can adjust it to 4 sockets, with 4 cores per socket. Please refer to this article which might help.

Best Regards,
Amelia

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 5

Since you only have one core per socket, you have 16 sockets. SE is limited to 4 sockets, which in your case means 4 cores. You need to up the number of cores per sockets, meaning down the number of sockets.