Why is allocation always selecting the same machine?

If you are running PowerPivot for SharePoint on more than one backend app server, then it is a common issue that folks are seeing only one server being used. It turns out that this might be ‘by-design’ so let’s talk about it for a bit. Let’s look at the two allocation methods we support:

1. Round-robin (the default) – This algorithm selects first one app server; then the next; then the next; until it loops back around. Since the actual marker for what is “the next” is kept in the proxy for the PowerPivot service application, the net-effect of this in practice is that the selection looks different from what you would expect. The behavior is a lot closer to random rather than sequential. Being random, and with a low number of servers to pick from (for example, 2), you should naturally expect that one app server might seem to be biased. Add more servers and you will see less bias.
2. Health-based – This is the one that most large shops will likely choose. The idea behind health-based is that the system will decide which is the ‘best’ app server for a machine. So when the allocation appears bias, it seems like health-based isn’t working. In reality, health-based may be doing exactly what was intended. If all machines have memory available (i.e. none of them are under memory pressure), then health-based uses CPU to break any ties, i.e. which ever CPU has the most CPU free wins. Let’s take an example:Suppose you have two machines “A” and “B”. Both are running PowerPivot. They both have 32GB on them with four quad-core processors. Rather than being dedicated to PowerPivot, “A” also doubles as the backend app server for Excel Services. Likewise, “B” doubles as the backend app server for PerformancePoint Services. So long as Excel Services and PerformancePoint consume similar CPU time, then databases will be loaded back and forth between “A” and “B” as one is more lightly loaded than the other. However, if PerformancePoint is lightly used and “B” is consistently less loaded (CPU-wise), then you will see PowerPivot databases being allocated to “B” until it becomes under memory pressure, and then allocation will shift to “A”.

All-in-all, health-based is still the best algorithm for large shops. Remember that the PowerPivot engine is an in-memory system. Our first goal is to get databases allocated wherever memory is available – balancing across the farm is not a priority.

Enjoy.