{"id":3437,"date":"2021-08-30T08:00:00","date_gmt":"2021-08-30T05:00:00","guid":{"rendered":"https:\/\/www.dataplatform.gr\/?p=3437"},"modified":"2023-05-16T22:03:04","modified_gmt":"2023-05-16T19:03:04","slug":"giati-o-sql-server-den-kanei-chrisi-ola-ta-cpu-cores","status":"publish","type":"post","link":"https:\/\/www.dataplatform.gr\/en\/giati-o-sql-server-den-kanei-chrisi-ola-ta-cpu-cores\/","title":{"rendered":"Why doesn't SQL Server use all CPU Cores?"},"content":{"rendered":"

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.<\/p>\n\n\n\n

The image we will see if we go to Activity Monitor<\/strong> in SQL Server Management Studio is that the chart never exceeds as below:<\/p>\n\n\n\n

\"\"
1<\/figcaption><\/figure>\n\n\n\n

By running the following query we can see which CPU cores are available:<\/p>\n\n\n\n

select cpu_id,scheduler_id,status,is_online from sys.dm_os_schedulers order by scheduler_id;<\/pre>\n\n\n\n
We see that out of the 12 cores, 4 are shown as offline:<\/p>\n\n\n\n
\"\"
2<\/figcaption><\/figure>\n\n\n\n
Where could it be due to?<\/h2>\n\n\n\n
Affinity mask<\/h4>\n\n\n\n
The first thought is to have one set Affinity Mask<\/a> in which it has been defined that only specific CPU cores are used by the threads.<\/p>\n\n\n\n
We can check if it is enabled with the following query:<\/p>\n\n\n\n
sp_configure 'show advanced option',1;\nreconfigure;\ngo\nsp_configure\ngo<\/pre>\n\n\n\n
We see that it has the default values, so that's not it.<\/p>\n\n\n\n
\"\"
3<\/figcaption><\/figure>\n\n\n\n
Resource Governor<\/h4>\n\n\n\n
By Resource Governor<\/a> (only for enterprise edition) we can define how many CPU, I\/O resources a user group consumes.<\/p>\n\n\n\n
But we see that this is also disabled:<\/p>\n\n\n\n
\"\"
4<\/figcaption><\/figure>\n\n\n\n
SQL Server Edition<\/h4>\n\n\n\n
But could the version of SQL Server be to blame? Yes maybe!!<\/p>\n\n\n\n
Checking the version we see that it is Standard Edition:<\/p>\n\n\n\n
\"\"
5<\/figcaption><\/figure>\n\n\n\n
In accordance with manual<\/a> of Microsoft, the Standard edition in SQL Server 2019 has the limitation of 24 cores and up to 4 sockets:<\/p>\n\n\n\n
\"\"
6 (from docs.microsoft.com)<\/figcaption><\/figure>\n\n\n\n
By running the following query we can see how many sockets the Server has:<\/p>\n\n\n\n
select cpu_count\/hyperthread_ratio from sys.dm_os_sys_info<\/pre>\n\n\n\n
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%.<\/p>\n\n\n\n
To solve the problem is all we have to do if the Server is a Virtual Machine<\/strong>, 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.<\/p>\n\n\n\n
After this change we will see that we now see all CPU cores online:<\/p>\n\n\n\n
\"\"
7<\/figcaption><\/figure>\n\n\n\n
Sources:<\/h2>\n\n\n\n