October 29, 2015 at 7:11 am
Hello colleagues. We have an issue with one customer where we support SQL services. The customer uses some kind of cloud virtualization and the issue is that SQL cannot use all of CPU cores that are assigned to the VM and OS. So my question would be:
Are there some limitations for number of cores that can be used by SQL Service?
We are running SQL 2008 R2 Enterprise Edition, so it should not be Edition or license related issue I guess.
There is no affinity configured on SQL level nor on the OS level for the SQL process.
Following query gives me following result:
select scheduler_id, cpu_id, status, is_online
from sys.dm_os_schedulers
where status = 'VISIBLE ONLINE' or status = 'VISIBLE OFFLINE'
05VISIBLE ONLINE1
16VISIBLE ONLINE1
27VISIBLE ONLINE1
38VISIBLE OFFLINE0
49VISIBLE OFFLINE0
50VISIBLE ONLINE1
61VISIBLE ONLINE1
72VISIBLE ONLINE1
83VISIBLE ONLINE1
94VISIBLE ONLINE1
And yes now the perhaps most obvious part, our cloud solution provider is delivering us 10 core VMs with two 5-cores CPUs. My believe is that this is the reason why SQL cannot use all 10 cores but is only using 8 of them. But I haven't found any explanation I could give to cloud provider to make them use some reasonable numbers of cores like 4-8-12-16.
I would appreciate any feedback on this topic. Thank you.
October 29, 2015 at 7:20 am
The maximum number of processors 2008R2 can see is 8.
I am guessing the cpu's your seeing have all been set to 10x1 core vCPU's instead of 2x5core vCPU's
October 29, 2015 at 7:43 am
Hi Anthony, thank you very much for fast response. I did not really thought about this, you are absolutely right about the vCPU configuration, its really added as 10x1-core CPU. I didn't even know that Enterprise Edition was ever limited on CPU count. Even SQL 2008 R1 seems to be OS max limited only and in R2 they changed to 8 CPUs.
So thanks again.
October 29, 2015 at 7:52 am
Yeah, they changed the licensing model as well between 2008 and 2008R2 with the addition of the Data Center edition.
We toyed with the idea of upgrading but would of cost to much to switch the 2008 Enterprise to 2008R2 Data Center licenses to keep the unlimited visualization rights, so the company decided not to pursue that option due.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply