Creating a bell curve in excel – VMware

In reviewing the VMware performance data in my cluster, I wanted to identify what the average utilization was. Specifically, I wanted to know what the average MHz per Proc utilization was. The goal eventually being to put limits on the CPUs (x MHz per proc) and if you need more speed, then you buy more CPUs. This is similar to the “Compute Unit” model done by many cloud providers.

STEP 1: Get the average CPU for the last month
Using the below query, I get a report of the system name, number of CPUs, average MHz utilization for the last month, and average MHz/CPU
select v.Name
, v.NUM_VCPU
, avg(sd.stat_value) ‘AvgTotalCPU’
, avg(sd.stat_value)/v.NUM_VCPU ‘AvgProc’
from vpxv_VMs v
inner join vpxv_entity e on v.HostID=e.ID
INNER JOIN vpxv_entity_moid m (nolock) on m.EntityID=v.VMID
INNER JOIN dbo.VPXV_HIST_STAT_monthly sd (NOLOCK) on sd.ENTITY=m.MOID
WHERE e.type_id=1
and stat_name=’usagemhz’
AND STAT_ROLLUP_TYPE=’average’
and sample_time > getdate()-31
group by v.Name, v.VMID, v.NUM_VCPU
order by avg(sd.stat_value)/v.NUM_VCPU desc

STEP 2: Create the normal distribution in Excel
Using the information from http://www.tushar-mehta.com/excel/charts/normal_distribution/index.htm, I imported the data into excel. Following the instructions I created the AVERAGE and STDEV functions and then exported them to a chart.