Optimizing Virtual Machine Density without Expensive Tools

I had to manually organize virtual machines onto a maximum of three ESXi hosts with limited CPU, DISK and RAM resources. Each host had different limits. The optimization tool used to perform the operation was Microsoft Excel.  

Microsoft Excel can automatically determine the maximum or minimum value of one cell by changing other cells — in my example, we will setup Excel to minimize the number of physical hosts needed to run a certain number of virtual machines.

Microsoft Excel comes with a tool called Solver, which is an add-in. Solver is part of a suite of commands sometimes called what-if analysis tools. With Solver, you can find an optimal value for a formula in one cell — called the target cell — on a worksheet. Solver works with a group of cells that are related, either directly or indirectly, to the formula in the target cell. Solver adjusts the values in the changing cells you specify — called the adjustable cells — to produce the result you specify from the target cell formula. You can apply constraints to restrict the values Solver can use in the model, and the constraints can refer to other cells that affect the target cell formula.

With Microsoft Excel installed, setup the CPU, RAM and storage of the hosts and virtual machines.

Next we create a host "bucket" which consists of adjustable cells. The Microsoft Excel Solver will be allowed to automatically modify the values used in the bucket. The constraints being that each virtual machine is allowed to run only on one particular host at a time. 

For example, the constraints will tell Microsoft Excel Solver that a virtual machine is not allowed to use CPU on one host and RAM on another host. You can populate the bucket cells with "0". This is just a starting point that the solver will use when it starts working. 

We will set all of the Microsoft Excel Solver constraints later on. We can also apply conditional formatting to make "used" hosts appear in a different color. For the moment lets just setup the matrix.

The rightmost columns indicate whether a host is used or not. If it is used by a virtual machine then we set the cell for that host to a value of 1 as follows:


We then sum the cells into the "*Minimize" cell. This is our target or objective cell that we will tell Microsoft Excel Solver to use. Our objective is to minimize the number of hosts used, whilst meeting all of  the virtual CPU, RAM and storage conditions.

Next, we set our virtual machine resource mapping to host resource mapping based on the resource constraints.

Finally we set the Excel Solver parameters and hit the Solve button.
Happy optimizing!