SQL Server does not use all assigned CPUs on VM

By:   |   Updated: 2017-04-14   |   Comments (7)   |   Related: More > Virtualization


Problem

As Virtual Machines (VMs) are getting more powerful, we have been migrating many SQL Servers into VMs and recently after we did a P2V (Physical to VM) migration, we started to notice performance issues.  On the VM, only half of the CPUs were 100% while the others were less than 20%. We checked to make sure there was no CPU Affinity Masking assigned and even tried adjusting the "max degree of parallelism" to make sure there was no MAXDOP hints on the queries.  Even after checking these, the VM still only used the first 4 CPUs out of the 8 CPUs.

Here is what it looked like in Windows Task Manager for the server with 8 CPUs.  As you can see only 4 processors are being heavily utilized.

Only 4 CPU being high
Solution

After some research, we found that the issue was due to a combination of the SQL Server edition (Standard Edition that we were using) and the VM configuration.

When using a VM with SQL Server standard edition, there are some limits for how CPUs are utilized.  On the Compute Capacity Limits by Edition of SQL Server page, if you have standard edition, the VM is limited to the lesser of 4 sockets or 24 cores, as shown below.

CPU License Map per Edition

Check Available CPUs with a SQL Server Query

We can use this query to check how many CPUs SQL Server sees and is using.

SELECT scheduler_id, cpu_id, status, is_online FROM sys.dm_os_schedulers 
GO

If your SQL Server has 8 CPUs, but only 4 show "VISIBLE ONLINE", this means you only can use a maximum of 4 CPUs no matter what.

Visiable Offline

Check Available CPUs from VM Configuration Manager

You may need help from your System Administrator if you do not have permission to access the VM Host.

Looking into this, we noticed that our automated VM build put the "Number of virtual sockets" equal to the number of total CPUs (12 in our case) and put "Number of cores per socket" equal to 1.  This equates to 12 cores, which is what we wanted, but this violated the limit of 4 sockets, so SQL Server standard edition only recognized 4.

To assign 12 cores to the VM, so SQL Server can use all of them we needed to adjust the VM Configuration as follows. This way there are only 3 sockets, with 4 cores per socket, giving us a total of 12 cores.

Visiable Offline

Multi Server Query to Check All Servers

My company has many SQL Servers, so it isn't easy to check every server. Luckily, if you have Central Management Server configured, you can run the query below to check all registered CMS servers.

----------------------------------------------------------------------------------------------------------------
-- CPU VISIABLE ONLINE CHECK
----------------------------------------------------------------------------------------------------------------
DECLARE @OnlineCpuCount int
DECLARE @LogicalCpuCount int

SELECT @OnlineCpuCount = COUNT(*) FROM sys.dm_os_schedulers WHERE status = 'VISIBLE ONLINE'
SELECT @LogicalCpuCount = cpu_count FROM sys.dm_os_sys_info 

SELECT @LogicalCpuCount AS 'ASSIGNED ONLINE CPU #', @OnlineCpuCount AS 'VISIBLE ONLINE CPU #',
   CASE 
     WHEN @OnlineCpuCount < @LogicalCpuCount 
     THEN 'You are not using all CPU assigned to O/S! If it is VM, review your VM configuration to make sure you are not maxout Socket'
     ELSE 'You are using all CPUs assigned to O/S. GOOD!' 
   END as 'CPU Usage Desc'
----------------------------------------------------------------------------------------------------------------
GO

This is the output from the above query and you can see if your configuration is good or if you have issues.

CMS

Note: This issue was found by one of my colleagues, Vara Thelu, so I cannot take the full credit for this tip and he gave me permission to share this tip.

Next Steps





get scripts

next tip button



About the author
MSSQLTips author Kun Lee Kun Lee is a database administrator and his areas of interest are database administration, architecture, data modeling and development.

View all my tips


Article Last Updated: 2017-04-14

Comments For This Article




Monday, January 9, 2023 - 5:10:39 AM - Jozef Back To Top (90806)
Hi,
I am sort of lost with all the core, sockets, settings etc....Can you advise how should I set the VM to assign all CPUs to VMs correctly?
Physical Server 1 production - 4x CPUs, each with 8Cores
Physical Server 2 (Replications only) - 4x CPUs, each with 6cores
Please advise what would be the ideal CPU settings for each VMs, to split ALL resources available between :
1.Terminal Server (12users)
2. SQL server (programs running on Terminal server)
3. PostgreSQL (eshop)
4. Linux VM with eshop
5. (5 other low-iops virtual machines)
It is unclear to me , how to configure it correctly for scenario for example :
- if no resources are needed on Terminal server, BUT all resources will be required by eshop (heavy traffic on eshop) so all CPUs could be used by eshop.
And the other way the same :
- no eshop resources are needed, BUT all resources are required on TERMINAL).

Seems the main bottleneck I have now is the SQL , which is using 1 core only on 100%, affecting significantly performacne of all other users, yet when I run the sql queery "SELECT scheduler_id, cpu_id, status, is_online FROM sys.dm_os_schedulers " it shows 12x CPU VISIBLE ONLINE and IS_ONLINE=1 for all of them.

Can anybody help please?

Friday, October 28, 2022 - 10:16:30 AM - Arjun BS Back To Top (90640)
But then in this case after changing the VM configuration wouldn't it incur more costs since now instead of 4 cores we are using 12 cores (ie 4 cores per socket ) . So now we have to license all the 12 cores instead of the earlier 4 cores . I am not sure what impact it would have on the licensing. Can someone explain ?

Monday, December 28, 2020 - 10:50:48 AM - Jacky ZHONG Back To Top (87958)
Thanks a lot for the sharing. We had the same server performance issue. And the VM configuration is the root cause. We followed the post and resolved the performance issue finally. Thanks again for the helpful sharing!

Friday, September 4, 2020 - 5:59:58 AM - Pieter Back To Top (86414)
Thanks, your tip saved our day!

Monday, August 10, 2020 - 8:02:21 AM - MrCalvin Back To Top (86264)
THANK YOU, couldn't understand why my VM SQL was only using one core. It turned out my VM was configured to 2 physical CPUs with each 1 core, instead of 1 CPU with 2 cores. Using SQL express, it seem only 1 CPU(socket) are supported.

Wednesday, June 26, 2019 - 10:34:23 AM - Tim Cullen Back To Top (81594)

Thanks for the great article, Kun! I ran into it serendipitously and it greatly assisted us with some of our issues!!

Tim Cullen


Wednesday, April 19, 2017 - 2:02:31 PM - Fran Back To Top (55041)

 Thanks for sharing the tips! That's what I needed. After virutaling the servers, it is very confused to figure the vCores and vProcessor, etc.

 















get free sql tips
agree to terms