SQL Server does not use all assigned CPUs on VM

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


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

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 

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.

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 

     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'

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


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 Started Now - Click here to get your free 14 day trial of SolarWinds Database Insights

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, 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