Option to check Memory usage during refresh to finally decrease the Memory usage

[stanbeamish]

Hi,

I have a dataset of 100MB size. When I refresh this dataset on the Service, it returns with a Memory exception. Saying the capacities available 3GB had been exceeded.

I know, when refreshing a dataset, at least the size of the dataset is taken into the RAM, but why 30 times the size in my case seems to be alot.

How is it possible to check, which part is consuming the memory?

I tracked already msmdsrv.exe when refreshing the same report in Power BI Desktop.

Here utilized RAM raises to >1GB (obeserved a 1.182 at most)

But then, still how to check which part is using the memory? Also after Refresh on PI Desktop the allocated RAM is never be released again. It stays at 0.9GB.

Also I used the SQLServer profiler to track the refresh metrics. Followed this very good article (https://dax.tips/2021/02/15/visualise-your-power-bi-refresh/). The output of this report shows no problems on the Queries to ADX (the slowest now 11 secs) and no excessive Processing times on one of the tables.
And here also, no reporting on memory usage.

Is there any other path to descrease Memory usage during refresh but eliminating all calculated columns and tables? Since the report makes use of the some of these.

Any ideas welcome and thanks in advance!

---Addendum (1 hour later)---

I have removed every calculated table and calculated column from the report (vpax shows 0 calculated table, and 0 calculated columns). Still for the DbSet-Size 110MB the Power Service returns on Refresh with a Memory Exception:

"Resource Governing: This operation was canceled because there wasn’t enough memory to finish running it. Either increase the memory of the Premium capacity where this dataset is hosted or reduce the memory footprint of your dataset by doing things like limiting the amount of imported data. More details: consumed memory 2983 MB, memory limit 2964 MB, database size before command execution 107 MB"

[stanbeamish · Accepted solution]

Hi community,

until today, we had no solution, but finally found the problem. But as we have found a solution, I try to explain it and write it down, so if anyone has the same problem, must not search any longer - hopefully.

This problem is only related to when using the Azure Data Explorer (ADX) to read data from.

The ADX connector in Power BI has some issues (or is not meant to be used for >300.000 records/~25 columns), and when instead using the SQL endpoint of ADX, the memory problems on refreshing on the Power BI Service do no longer occur. The ADX connector seems not to be efficient when loading data and thus makes extensive use of the memory.

That said, when connecting to an ADX, it is recommended to:

1. Click the down arrow on Get data from the ribbon and select More...
2. In the Get Data dialog search for Azure SQL database 
3. Choose Azure SQL database and click Connect - (!! choose NOT Azure Data Explorer (Kusto))
4. Enter the ADX endpoint uri WITHOUT "https://" into Server (e.g. "[your server].[location].kusto.windows.net")
5. Choose Import or DirectQuery to your needs
6. In the Credentials dialog, choose on the left Microsoft account. (If you have problems on this, you may have to cleanup in the PowerBI Desktop Options&Settings > Data source settings menu the corresponding data source settings)
7. Sign in to your account
8. Click Connect
   The Navigator dialog shows you the databases and tables from which to choose yout data from

This process at least solved out problem on extensive memory usage (error on using >3GB on refresh), although the overall dataset on the service has a size of ~100MB.

Maybe this helps someone else. 

[v-jayw-msft]

Hi @stanbeamish ,

You may optimize the dataset.

Please take a look at this document.

https://learn.microsoft.com/en-us/power-bi/enterprise/service-premium-capacity-optimize .

Best Regards,

Jay

[stanbeamish]

Hi,

thank you for your message.

Unfortunately, it does not take me any further.
I have startet with an empty report and add the tables one by one. No visuals, no data relations in Power BI. Still the refresh shows a Memory exception for the report that has 100MB.
As soon as I have a solution I will post it here.

Regards