Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Kris_KB
New Member

monitor memory usage during dataset refresh / SQL Profiler / weird PeakMemory usage metrics

I'm a begginer with PBI so I'll try my best to explain problem in details. If more info or explanation needed please ask.

So in my company recently we had a problem with one of the datasets to refresh, SOMETIMES it was dropping an error:

 

Kris_KB_7-1718985274596.png

 

"Error

Data source error: Resource Governing: This operation was canceled because there wasn't enough memory to finish running it. Either reduce the memory footprint of your dataset by doing things such as limiting the amount of imported data, or if using Power BI Premium, increase the memory of the Premium capacity where this dataset is hosted. More details: consumed memory MB, memory limit MB, database size before command execution MB. See https://go.microsoft.com/fwlink/?linkid=2159753 to learn more. Table: Facts."

 

Of course it clearly states it run out of memory. We store datasets in PowerBI Embedded, this particular dataset sits on A2 size PBIE so 5GB of RAM memory:

Kris_KB_0-1718983625089.png

 

Dataset itself according to Tabular (Vertipaq analyzer) takes 2.48GB:

Kris_KB_1-1718983800378.png

 

Here's the question No. 1: Does dataset during the refresh require "doubled" space as it holds "old" copy when creating refreshed one? But even so, it should just fit A2 size PBI Embedded memory.

 

So the question was born, how much memory we need during refresh process? I was looking for the answer and found on google that I can run SQL Profiler connected to the workspace via XMLA endpoint to trace what is going on with the dataset.

Kris_KB_2-1718984129921.png

 

 

I successfully connected and traced some smaller models as the one from above 2.48GB takes 35 minutes to refresh. 

 

I took two models for tests:

  • 1. Tiny 5.74MB model. 

Kris_KB_3-1718984300281.png

 

SQL Profiler showed Peak Memory usage as 93603 KB which is 93.6MB! Is that possible? It's almost 20x more than dataset itself! If number is real how 2.48GB model was able to refresh? In theory it would require around 50GB of memory... (20x more)

 

Kris_KB_4-1718984486881.png

 

  • 2. Same thing with second dataset I've tested. Tabular Editor reports it's size as 27.7MB

Kris_KB_5-1718984740397.png

 

When SQL profiler reports PeakMemory: 277084 KB = 277MB! Now 10x more than dataset itself according to Vertipaq analyzer from Tabluar Editor. Same question, how 2.48GB model was able to refresh then?

 

Kris_KB_6-1718985013783.png

 

As a help with the above I was using Chris Webb blog:

https://blog.crossjoin.co.uk/2023/04/30/measuring-memory-and-cpu-usage-in-power-bi-during-dataset-re...

 

So can someone explain to me do I collect the right stats and if yes how the refresh works and how much memory it needs?

 

 

2 REPLIES 2
SaiTejaTalasila
Solution Sage
Solution Sage

Hi @Kris_KB ,

 

Memory and storage are different.Memory utilisation increases with increase in number of transformation steps.

Always try to filter your data first and apply your functions(grouping,sum etc,..) next.

 

Thanks,

Sai Teja 

 

Thanks,

Sai Teja 

lbendlin
Super User
Super User

 question No. 1: Does dataset during the refresh require "doubled" space as it holds "old" copy when creating refreshed one? But even so, it should just fit A2 size PBI Embedded memory.

yes, it needs twice as much space (plus a bit).  And no, it will no longer fit if you refresh the entire dataset. Try refreshing individual partitions.

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.