Power BI Desktop for Report Server (May 2022) unable to Apply recent changes because Out of Memory

[BinShebrain]

Environment:

Windows 10 PC

16GB Memory

Power BI Desktop for Report Server (May 2022)

Issue:

Report was working fine before, there is a need to include more rows of data so I adjusted my WHERE clauses. When I try to Apply these changes it loads the rows then tries to process them but eventually stops with the error: 'There's not enough memory to complete this operation. Please try again later when there may be more memory available. '.

Troubleshooting:

1. In Power BI Desktop I tried increasing allowed memory in File -> Options and settings -> Options -> GLOBAL -> Data Load -> Maximum memory used per simultaneous evaluation (MB). The change was from the default of 432 to 6144. The options menu didn't allow this number until I also changed Maximum number of simultaneous evaluations from 8 to 2. (Note: I later learned these changes only take effect the next time Power BI Desktop starts so I'm skipping to the part after I realized that).
   1. Result: Attempting to Apply now fails very early with the error message: "Specified argument was out of the range of valid values. Parameter name: maxWorkingSetInMB". This is a completely different error that I think is worth reporting as a bug because after a lot of trial and error this particular message stops appearing when the setting is lowered to 4095. After which the original insufficient memory error is back when trying to Apply.
2. I discovered it's possible to connect to the internal SSAS instance that temporarily gets created while Power BI Desktop is running. I tried playing around with the Low/Total/Hard/Query memory limit settings but the only thing I managed to do is make the same insufficient memory error appear either sooner or much sooner than expected in unpredictable ways, even after learning that these settings are treated as a % for values 1-100 and are treated as bytes otherwise.
3. I found the log files related to the SSAS instance and this is where things get interesting. The logs are in AppData\Local\Microsoft\Power BI Desktop SSRS\AnalysisServicesWorkspaces\AnalysisServicesWorkspace_RandomGUIDHere\Data\msmdsrv.log. Whenever the error of insufficient memory error happens I find the below messages logged.

(10/06/2022 3:45:35 PM) Message: Entering Low Memory mode (Current memory consumption: 3313.1480 MB). (Type: 1, Category: 289, Event ID: 0x41210023)
(10/06/2022 3:45:36 PM) Message: Low Memory situation has deteriorated [Stage: FORCE_CLEAN] (Current memory consumption: 3341.0850 MB, Low Memory Limit: 2662.3180 MB, First Cleanup Limit: 2967.5080 MB, Second Cleanup Limit: 3272.6990 MB, High Memory Limit: 3272.6990 MB, Third Cleanup Limit: 3274.6990 MB, Final Cleanup Limit: 3276.6990 MB, Hard Memory Limit: 3276.6990 MB). (Type: 1, Category: 289, Event ID: 0x41210024)
(10/06/2022 3:45:36 PM) Message: Low Memory situation has deteriorated [Stage: FINAL_CLEAN] (Current memory consumption: 3381.2770 MB, Low Memory Limit: 2662.3180 MB, First Cleanup Limit: 2967.5080 MB, Second Cleanup Limit: 3272.6990 MB, High Memory Limit: 3272.6990 MB, Third Cleanup Limit: 3274.6990 MB, Final Cleanup Limit: 3276.6990 MB, Hard Memory Limit: 3276.6990 MB). (Type: 1, Category: 289, Event ID: 0x41210024)
(10/06/2022 3:45:37 PM) Message: Low Memory situation has deteriorated [Stage: CANCEL_JOBS] (Current memory consumption: 3388.8470 MB, Low Memory Limit: 2662.3180 MB, First Cleanup Limit: 2967.5080 MB, Second Cleanup Limit: 3272.6990 MB, High Memory Limit: 3272.6990 MB, Third Cleanup Limit: 3274.6990 MB, Final Cleanup Limit: 3276.6990 MB, Hard Memory Limit: 3276.6990 MB). (Type: 1, Category: 289, Event ID: 0x41210024)
(10/06/2022 3:45:37 PM) Message: Entering Low Memory mode (Current memory consumption: 3329.3350 MB). (Type: 1, Category: 289, Event ID: 0x41210023)

After that I tried playing with various settings both from Power BI Desktop side and/or from the internal SSAS side, but the Hard Memory Limit that shows in these logs NEVER went higher than 3276.6990 MB even when both sides are configured to have a limit higher than that. Repeating the same experiments on another machine with 32GB RAM gives the same results.

Please tell me I discovered 2 bugs and I was not too tunnel visioned to find some obvious solution to my predicamemt.

[v-xiaoyan-msft]

Hi @BinShebrain ,

The actual calculation consumes a considerable amount of memory. Basically it accumulates memory for all these steps, so even a small 10MB file can end up needing 1GB to refresh.

I don't think you can really observe how much CPU/RAM you are consuming unless you have premium capacity.
The best idea is to make sure you don't have any extra steps in your query to optimize your query. Also try to avoid refreshing all reports at the same time.

Best Regards,
Community Support Team _ Caitlyn

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

[BinShebrain]

Optimizations may be needed eventually, but the behaviors I'm describing are while I'm working in the desktop application (i.e. changes still not published) so it's annoying that the Desktop specs are the bottleneck instead of the Server specs. Makes me wish there was a "Apply changes but on server side" button.

One workaround I tried is save file and when it asks me about unapplied changes I choose "Apply later" but this seems to keep my work ready until next time I open the report in desktop app and the actual published report still doesn't have the extra rows even when I manually do a refresh on server side.

Premium capacity doesn't apply here because I'm working on the on-prem version of i.e. Power BI Report Server.