Power BI Desktop · Power Query

Speed Up Power Query In Power BI Desktop By Increasing Or Decreasing The Number Of Evaluation Containers

Last week I showed how the new MaxEvaluationWorkingSetInMB registry setting could increase the performance of memory-hungry Power Query queries in Power BI Desktop. In this post I’ll show how the other new registry setting, ForegroundEvaluationContainerCount, can also help performance. Before I carry on I recommend you read the documentation on these new registry settings if you haven’t done so already.

To illustrate the effect of this setting I created ten identical Power Query queries feeding an Import mode dataset in a new .pbix file, each of which read data from the same 150MB CSV file, apply the a filter and then count the number of rows returned. These queries don’t require a large amount of memory but do take a couple of seconds to execute:

With ForegroundEvaluationContainerCount not set, refreshing the entire dataset (with background queries disabled) initially showed ten active evaluation containers:

I’m pretty sure these containers were used to determine the schemas of the tables returned (see here for more background); these were then joined by ten more containers which I assume were actually used by the refresh:

With these default settings refresh took 18 seconds according to Profiler.

With ForegroundEvaluationContainerCount set to 3:

This time there were never more than three evaluation containers active at any one time:

…and refresh took 24 seconds.

So we’ve proved that by setting ForegroundEvaluationContainerCount to a low value we can limit the amount of parallelism and, in this case, make performance worse. So why would you ever want to limit the amount of parallelism like this? The maximum amount of memory available to an evaluation container isn’t just controlled by the MaxEvaluationWorkingSetInMB registry setting; as the docs say, the effective maximum is also determined by the number of evaluation containers used. So reducing the amount of parallelism can increase the amount of memory available to each evaluation container and possibly increase performance.

I then created twenty new copies of the Power Query query, bringing the total number of queries in the pbix file to thirty, and set removed the ForegroundEvaluationContainerCount registry key to go back to using the default settings. During refresh I saw that no more than twenty evaluation containers were active – as expected, because the docs state that with the default settings no more than twenty containers will be used. I’ll spare you the screenshot. Refresh took 62 seconds.

Then I set ForegroundEvaluationContainerCount to 30 and refreshed. This time I could see thirty evaluation containers being used during refresh, and refresh took 55 seconds – not a massive improvement, but an improvement that I’m pretty sure can be attributed to the increased parallelism (I suspect that there was some other bottleneck here, possibly IO).

In conclusion the ForegroundEvaluationContainerCount registry setting is another useful tool to improve refresh performance for Import mode datasets (it’s also useful for DirectQuery but that’s something for a future post) in Power BI Desktop. Finding the optimal value to set it too is not straightforward though and is likely to involve a lot of experimentation. As always, please let me know how you get on using it.

8 thoughts on “Speed Up Power Query In Power BI Desktop By Increasing Or Decreasing The Number Of Evaluation Containers

  1. B Sai Prasad – Chennai, Tamil Nadu, India – B Sai Prasad, PMP®, PMI-SP®, Microsoft MVP Project, Senior Manager - Learning & Development, has been with service provider Cognizant Technology Solutions India Pvt. Ltd since 2001 where he was named winner of the company's Global Trainer of the Year award. He has spent 13,000-plus hours in mentoring, coaching, training 9000-plus practitioners on project management topics ranging from project management concepts, project risk management, project scheduling, Microsoft Office Project® to software estimation techniques. He is a Champion of Project Management from PMI India and also Associate Champion Advisory Committee, PMI India. He is awarded the Champion of the Quarter (Q4 – 2012) and Delivery Excellence Award (2011-2012, 2012-2013) from PMI India. He's also the editor of the project management book, Forecast Scheduling with Project 2010. He is a Microsoft Certified Technology Specialist (MCTS) in Project 2010. He is the President of MPUG India Chennai chapter - http://chennai.mpug.com - to promote and help practitioners on how to effectively use Microsoft Office Project.
    B Sai Prasad says:

    Thanks Chris. How does this work in Power BI service?

    1. Chris Webb – My name is Chris Webb, and I work on the Fabric CAT team at Microsoft. I blog about Power BI, Power Query, SQL Server Analysis Services, Azure Analysis Services and Excel.
      Chris Webb says:

      You can’t change the number of containers in the Power BI Service, but if you are connecting through a gateway then you can change the number of containers there though. It’s complex and I’m still finding out how everything works…

  2. Hey Chris, do you happen to know if this is only for the MSI based install of Power BI or works for the MS Store version? We use the MS Store version of the software because our BA’s need it automatically updated without an install package to be created each time, but can’t seem to find the key location and there’s no documentation about the differences when using either version. We can create the key location manually if need be just checking if that’s required.

    1. Chris Webb – My name is Chris Webb, and I work on the Fabric CAT team at Microsoft. I blog about Power BI, Power Query, SQL Server Analysis Services, Azure Analysis Services and Excel.
      Chris Webb says:

      It does work for the Store version – I use both versions, and I tested both. I don’t remember having to create the key location manually though.

Leave a ReplyCancel reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.