ESX CPU at very low average but if I drop core count on SQL server it strgules

On ESXi 5.5 I have 2 x hosts, each host has 12 Cores, 2 socket x 6 core.
We have a bunch of servers running on them and have a total of 70 CPUs allocated out.
Our main DB server currently has 8CPU allocated, however if I drop it down to 4 is struggles.
Given that, over the course of the year and also month by month, we don't use more than an average of 10%
of the total CPU processing power on the ESX hosts, should this be happening?

I'm trying to figure out how many cores we need in our new ESX hosts and I'm confused by the fact that
although we're not using a lot of the overall CPU power, the SQL server struggles if I reduce the core count.
Should it not just take up more of the unused cores?
LVL 1
wannabecraigAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Andrew Hancock (VMware vExpert / EE MVE^2)VMware and Virtualization ConsultantCommented:
I would expect if you reduce the number of vCPUs from 8 to 4 on your SQL server, the performance would get worse.

SQL Server is CPU, Memory and Disk I/O heavy in some instances, and VMware would recommend allocate more resources in terms of CPU.

Remember that a virtual hypervised SQL server, compared to a physical SQL server is constrained, when it's virtualised, so does need more resources. (CPU).

HOST CPU is not often the bottleneck, but total memory is, we work on approx 5-6 vCPU per Core.

It can only use the vCPU that you have defined, e.g 4 or 8, if you allocate 8, if it cannot use all 8 it will not. But if you only allocate 4 vCPU, it cannot add any more....because those are the constraints of the VM configuration.
wannabecraigAuthor Commented:
"HOST CPU is not often the bottleneck, but total memory is, we work on approx 5-6 vCPU per Core."

Do you mean RAM?  There is a lot of RAM on the server, the SQL server uses all we allocate as you would expect.  What I dont understand is this.   Say on the host on which the SQL server is running has a total of 12 available Cores, we've allocated 8 to the SQL server and and the hosts stats say that that the total CPU usage, over an average of say a month is 18%. The we drop the CPU count on the SQL server to 4, why dont we see the host CPU avg shoot up as it consumes all of 4 CPUs in line with seeing the SQL servers CPU graph shoot up?

So basically, when I look at the graph on the individual machine I see it getting wacked, but the overall host performance doesnt seem to mimic it as much as I think it should.
Andrew Hancock (VMware vExpert / EE MVE^2)VMware and Virtualization ConsultantCommented:
SQL Server always uses as much RAM (memory) that you allocate, this is how it works, and then releases memory back to the server, when other applications request it!

When you are allocating vCPUs, remember these are scheduled vCPUs, you are not allocating a whole vCPU as in the physical world. (it's not the same, performance on an SQL server runs "slower" hypervised than it does on a physical server, because CPU time is time sliced).

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
VMware

From novice to tech pro — start learning today.