Why doesn't SQL Server use all CPU Cores?
Certifications:
- How do we automate the process of checking the integrity of databases in SQL Server without using a maintenance plan - 10 May 2024
- How can we connect Oracle Database to SQL Server using Oracle Gateway on Windows - 5 April 2024
- How can we get email whenever PGA memory exceeds a limit in Oracle database - 1 March 2024
Having a SQL Server Instance installation we can face the following strange situation, while there is a need for CPU usage that never reaches this 100%. In the article we will see what could be the reason and how to fix it.
The image we will see if we go to Activity Monitor in SQL Server Management Studio is that the chart never exceeds as below:
By running the following query we can see which CPU cores are available:
select cpu_id,scheduler_id,status,is_online from sys.dm_os_schedulers order by scheduler_id;
We see that out of the 12 cores, 4 are shown as offline:
Where could it be due to?
Affinity mask
The first thought is to have one set Affinity Mask in which it has been defined that only specific CPU cores are used by the threads.
We can check if it is enabled with the following query:
sp_configure 'show advanced option',1; reconfigure; go sp_configure go
We see that it has the default values, so that's not it.
Resource Governor
By Resource Governor (only for enterprise edition) we can define how many CPU, I/O resources a user group consumes.
But we see that this is also disabled:
SQL Server Edition
But could the version of SQL Server be to blame? Yes maybe!!
Checking the version we see that it is Standard Edition:
In accordance with manual of Microsoft, the Standard edition in SQL Server 2019 has the limitation of 24 cores and up to 4 sockets:
By running the following query we can see how many sockets the Server has:
select cpu_count/hyperthread_ratio from sys.dm_os_sys_info
In this case we had 6 sockets with 2 cores each, i.e. a total of 12 cores and due to version only 4 sockets were online which left us to use the 8 cores. So that was why the CPU limit was up to 66%.
To solve the problem is all we have to do if the Server is a Virtual Machine, is to change the number of sockets to up to 4 and increase the number of cores per socket e.g. 2 sockets with 6 cores per socket which again makes us 12 cores.
After this change we will see that we now see all CPU cores online:
