Learn more about SQL Server tools

mssqltips logo
giveaway
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

SQL Server does not use all assigned CPUs on VM


By:   |   Read Comments (1)   |   Related Tips: More > Virtualization

Attend a SQL Server Conference for FREE >> click to learn more


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


Last Update:


signup button

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
Related Resources





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


SQL tips:

*Enter Code refresh code     



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

 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.

 


Learn more about SQL Server tools