Planning to virtualize databases on vSphere 5.5. If I would to assign one to one core relationship it would be around 108 cores total. Is there a guidance on CPU capacity planning for ESXi host?
I'm assuming this refers to MSSQL databases? Quick, read this ... http://www.amazon.co.uk/Virtualizing-SQL-Server-VMware-Technology/dp/0321927753
I have always found that SQL benefits more from memory that CPU cores, and you also need to consider your MS licencing costs ... we found that it was better to use hosts with two physical CPU's (with the best clock speed), and loads of RAM. As always, a big part of this is understanding the workload requirements.
I have read many articles and none of them talk about the ratio. it all depends on the usage of the VM. You need to check the workload for your VM's and then over a period of time estimate what the vcpu per core ratio is
The most important things when it comes to vitalizing SQL is:
CPU - Making sure your NUMA nodes match up
Memory - Getting the right amount of memory to meet your applications requirements
Disk - The right Datastore / IO to make sure disk isn't holding up your SQL instance
SQL System Databases - Put the tempdb on the fastest disk - System Databases
CPU - Make sure your vCPU line up with your hosts hardware and just use vCPU sockets and not cores. Using 4vCPU with 2cores each will typically hurt performance as its harder on the scheduler. Only use the cores feature if you need to get around licensing issues.
Here is two articles on NUMA
As of ESXi 5.0+ if your VM has more then 8 vCPU it will automatically align the numa nodes for you so you don't have to worry about this as much
Memory - This you will need to determine based off you current SQL boxes and how much memory they are ACTUALLY USING. There is some SQL bench marking tools and queries you can do in SQL to find out how much Active Memory it is using which will be important as SQL typically eats up AS MUCH MEMORY as it can get its greedy little paws on. Say you have a physical server with 128GB of memory, running Windows Server 2008 R2. Chances are SQL will get up 120GB of that memory over time and will just cache it encase it needs it. Check to see how much memory is actually active in that cache. If your SQL box is only ever using 64GB of that memory it is safe to say you could get away with assign the VM 80GB of memory or something which gives a fair amount of overhead while claiming back memory that would otherwise not be used
Disk - I would create 3 Virtual Disks for your SQL system, possibly 4
C:\ OS & all system databases except tempdb - Placed on a slower Datastore on your SAN
D:\ Logs - Your fastest Datastore on your SAN
E:\ Databases & tempdb - Your fastest Datastore on your SAN
F:\ All your system databases if you like, if tempdb is in here then - Your fastest Datastore on your SAN
There is some other small tweaks you can do in SQL if you have VERY large queries with Transparent Page Sharing
After all the tweaks you can do you should also expect around a 5-10% performance hit on your SQL box. This for the most part can typically be mitigated with some of the newer SQL features in the new SQL2012 / 2014 products, however plan for it. You don't virtulize SQL to gain performance you virtulize it to gain all the high availability, backup options, and DR options that come natively in the virtual environment.
I hope this helps