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.
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.
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.
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.
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.
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
- Review Compute Capacity Limits by Edition of SQL Server and any other relevant pages to see if there is anything else you can tune.
- You can modify the query to check if the server is a VM or not and also put more logic for checking the edition of SQL Server.
- Please check out these other SQL Server Virtualization Tips.
