Power BI – Desktop increase the Power Query Performance settings

Sometimes you are tired of waiting forever on a refresh using the PBI Desktop

In the last versions if Power BI Desktop (May 2021) MS has increased the evaluation container default size,

from 256 Mb to 432 Mb per container.

But is some cases for large datasets this is not enough… it will take forever to refresh it on your desktop.

So if you have enough memory available you can set you own Memory settings as well as the number of Evaluation Contrainers

As described in this articel : https://docs.microsoft.com/en-us/power-bi/create-reports/desktop-evaluation-configuration

As you can see in my settings here :

When I start the refresh it spins up 20 evaluation containers

And it assignes 432 Mb to it for processing

If you have enough memory available you can increase the registry settings to allocate extra per evaluation container to speed up the fresh.

MaxEvaluationWorkingSetInMB

Keep in mind that if you set this too high, it will have an reverse effect …

Because if PBI runs out of memory it will start using the virutal memory,  using the PAGEFILE on disk…

This might already be the case if you are now running short on memory and delays you refresh …

You can monitor this using the Task Manager, add extra debug columns to see how it behaves.

The Page Pool is the main one to monitor…

Page Pool :

The amount of system-allocated virtual memory, in kilobytes, used by a process.

The paged pool is virtual memory available to be paged to disk.

Paging is the moving of infrequently used parts of a program’s working memory from RAM to another storage medium, usually the hard disk.

The paged pool includes all of user memory and a portion of system memory.

Here is an other article of the famous Chris Webb that shows the performance impact when changing the settings

https://blog.crossjoin.co.uk/2021/06/06/speed-up-power-query-in-power-bi-desktop-by-allocating-more-memory-to-evaluation-containers/

You can use MS RamMAP to do more in depth analysis

https://docs.microsoft.com/en-us/sysinternals/downloads/rammap

Other Debugging Tools are of course the built in Performance Analyzer in PBI Desktop.

This will help trace long running Visuals in your app.

More advanced tools are the MS SQL Profiler, see DAX Studio

PS : You need to have the SQL Profiler installed on your machine in order to use it.

See here on how to…

https://blog.crossjoin.co.uk/2019/02/09/power-query-execution-times-using-sql-server-profiler/

Enjoy !