Forum Discussion
Defragment not working
You're running into something called the Command Memory Limit, which is documented here: https://learn.microsoft.com/en-gb/power-bi/enterprise/troubleshoot-xmla-endpoint#resource-governing-command-memory-limit-in-premium It's also something I will address in an upcoming blog post in a series I started here https://blog.crossjoin.co.uk/2024/04/28/power-bi-semantic-model-memory-errors-part-1-model-size/ (reading this post will provide some useful background).
If you're using an A2 with 5GB of RAM and your existing model is 800MB, then you have 5GB - (800MB plus some extra memory used by queries and sessions) left for your refresh, so say 4GB. The next thing you should do is run a Profiler trace against your model (see https://blog.crossjoin.co.uk/2020/03/02/connecting-sql-server-profiler-to-power-bi-premium/ for how to do this) and capture the Command End events when you run a refresh. From this you'll be able to see the peak memory used during the refresh as a whole and for just Power Query (see https://blog.crossjoin.co.uk/2023/04/30/measuring-memory-and-cpu-usage-in-power-bi-during-dataset-refresh/) and for just Power Query for individual table partitions (see https://blog.crossjoin.co.uk/2023/04/02/identifying-cpu-and-memory-intensive-power-query-queries-during-refresh-in-the-power-bi-service/). My guess is that the peak memory for the refresh as a whole is going over 4GB. If so, then you need to look at the Power Query memory usage number for individual partitions and see if there are any memory hungry Power Query queries - if so, they need to be tuned. If not then it's likely to be your calculated columns that are the problem still. Reducing the amount of parallelism during refresh may also help reduce the peak memory usage (see https://blog.crossjoin.co.uk/2022/10/31/speed-up-power-bi-dataset-refresh-performance-in-premium-or-ppu-by-changing-the-parallel-loading-of-tables-setting/).
If peak memory for the refresh is a lot less than 4GB then it's possible that the problem isn't the refresh but that users are running memory-hungry queries during the refresh. Running a Profiler trace during a refresh and looking for Query Begin/End events will tell you if queries are being run; there is a new Profiler event coming very soon which will tell you more about query memory usage but before that happens it's impossible to know how much memory a query consumes in the Service but you should see it happen easily in Power BI Desktop by looking at what happens in Task Manager when you view or interact with a report. If this is the problem then you'll need to tune your model and measures to reduce memory usage. This antipattern is a very common example of DAX that can cause memory spikes: https://xxlbi.com/blog/power-bi-antipatterns-9/
If you are able to post some screenshots of your Profiler traces and the memory usage numbers please let me know - I'm curious to see what you find!
HTH,
Chris
Thank you very much cpwebb . Very thorough and great information. I really appreciate it!
I used Profiler for two different datasets, and the following are the results:
1) The refresh failed for this dataset:
PeakMemory: 4.5GB , MashupPeakMemory: 1.49 GB
I checked the 'progress report end' for all the tables and observed that the MashupPeakMemory for most tables falls between 120MB and 250MB, except for one table, which was 1.4 GB!
2) This is another dataset with an incremental refresh, and the refresh failed:
PeakMemory: 3.95 GB , MashupPeakMemory: 2.12 GB
And same story with this dataset, as MashupPeakMemory for one of the tables was 1.65 GB!
I refreshed the same dataset again, and it refreshed successfully with a PeakMemory of 3.82GB, as follows:
As you mentioned, I think I need to tune the queries in Power Query for this table that is eating lots of memory, correct?
This is the query for that table:
Thanks again! much appreciated!
Thanks for the detailed information. I'm still learning a lot about this topic and it helps to have a real life example to work though!
It does sound like there's some tuning needed in your Power Query queries. Bear in mind that for the Command End for the refresh you're seeing peak values for all operations, so if you're refreshing 6 tables in parallel then that peak value could be 6 * (a reasonable amount of memory for a single table). From my limited experience I would say that 200-400MB is a reasonable value for memory used by Power Query for a single table.
That said, a MashupPeakMemory of 1.4GB or 1.6GB is definitely too high. For the Power Query query you posted, how much of that folds? Do the Merge and the Group operations fold? They would be the obvious memory-hungry operations; if they do, the next thing to investigate would be the steps where you're changing data types; moving them to the end of the query, or at least after the Merge, could help.
Thanks again cpwebb for your valuable comment.
I removed all the transformations and started from scratch, and noticed that the groupby (Table.group) query is not folding. Also as you mentioned, changin data type needs to be done after the Merge.
I tried a lot, but I wasn't able to fold the Groupby function, so I created a view in SQL server for the same table with same steps and columns. After refreshing the dataset with this view, and tracing the Profiler, the MashupPeakMemory dropped from 1.4GB to 140 MB for that table, and PeakMemory in CommandEnd for all operations was 3.4 GB.
I followed the same steps for another small dataset, and even though the MashupPeakMemory for that specific table dropped from 1.65GB to 120 MB, the refresh failed again. After some tests, I found that removing 4 calculated columns resolved the issue (I had another table with 9 Million rows and 4 calculated columns).
Below is one of the calculated column that is causing the issue (all other columns have exactly the same function):
Even I had optimized this query before by changing CALCULATE to MAXX!
By removing these 4 calculated columns the PeakMemory for all operations dropped to 2 GB.
I will try reducing the amount of parallelism to see how much PeakMemory changes.
I guess the challenge is to identify which calculated column is consuming more memory. With a larger dataset containing more columns, it becomes difficult to pinpoint the memory-hungry calculated columns.
Thanks again!
I'm glad to hear you've made progress - pushing transforms as far upstream as possible is always a good thing.
Regarding your calculated columns, and DAX expression that uses FILTER() on a whole table always carries a risk of a memory spike. How many rows are in the FollowUps table? It could be that an expression something like this would be better from the point of view of memory:
Response(patient contact)_= var CurrentInspectionId = SELECTEDVALUE(FollowUps[InspectionId]) return CALCULATE( MAX(FollowUps[Response]), FollowUps[InspectionId] = CurrentInspectionId, FollowUps[QuestionId] = "973")Apologies for any typos/syntax errors, or if this is exactly what you had before you optimised.
I don't think there's a way of identifying how much memory each calculated column in a table uses during a refresh, so it will have to be a process of elimination - you'll need to add each calculated column individually, refresh and see how the memory usage is affected.