I mentioned this in a recent post, but I figure a picture illustrates it better. This is a screenshot of task manager as a PowerPivot refresh completes. This one ran for about 30 minutes, so this represents just the tail end of the process:
Note the two highlighted points – one of the 4 CPU’s pegged at 100% for awhile – and while the other 3 did go “quiet” at the very end, they WERE active for part of the time that the fourth CPU was at 100. So – CPU gets precious near the end of refreshes (after hovering around 50% for much of the process).
Even more notably, RAM usage spiked by nearly 2 GB! The PowerPivot file in question was 1.45 GB on disk when it was complete. And that workbook was still in RAM after refresh completed, so the 2 GB spike was pure overhead during the final compression process.
(Side effect: Even if you have enough RAM to load a workbook, that does not mean you have enough RAM to refresh it.)
Allocate your server RAM and CPU around the refresh process, folks.