Memory blow out importing Excel/XML files

[SteveCarter1]

Hi
I have a Windows Server 2016 Std with 8 cores / 32GB in Azure. It's only job is to run my on-premises data gateway.

I have 1 report that uses imported Excel & XML formatted files extensively, however the total size of all those files (only 21 files) is only around 5MB. I know compression plays a part but we are still not talking about billions of rows or columns.... maybe around 50k rows in total with about 25 columns - most data is from drop down lists so is quite repetitive.

The data gateway is crashing with out of memory exceptions.  Watching it as this specific report refreshes, the service blows out to 31GB of memory, then this:

Faulting application name: Microsoft.PowerBI.EnterpriseGateway.exe, version: 3000.162.10.0, time stamp: 0xcc25b00a
Faulting module name: clr.dll, version: 4.8.4614.0, time stamp: 0x63995bc5
Exception code: 0xc00000fd
Fault offset: 0x000000000003c844
Faulting process id: 0x1ac8
Faulting application start time: 0x01d94bc496309199
Faulting application path: C:\Program Files\On-premises data gateway\Microsoft.PowerBI.EnterpriseGateway.exe
Faulting module path: C:\Windows\Microsoft.NET\Framework64\v4.0.30319\clr.dll
Report Id: 589e3650-fc43-4e19-a082-8fd9eeb542c2
Faulting package full name:
Faulting package-relative application ID:

Yes the report does a fair bit of data manipulation but still... I don't duplicate tables and have only a couple of merges.  I can also successfully refresh the report on my lowly Intel i5 12GB laptop running Win10 using the exact same files as input so figure something odd is going on if a more efficient server with 3x RAM cannot do this.

Can anyone suggest some tricks or what limits I can be aware of in order to ensure I keep this report under relative memory control?

[SteveCarter1 · Accepted solution]

Okay this was weird.  I hadn't touched development of this report for a while - at least a few months.

I downloaded it from the service, opened it in Power BI Desktop (Feb 2023 release), then I made a change to a text box, manually refreshed it then uploaded back to the service.

Now, with the same input files, the gateway service only uses 500MB more than at idle.

I've refreshed within the service and it's the same. So it appears to be 'resolved'.

If anyone else comes across this, have a think how old the report is and I guess try downloading it, making an insignificant change just so the report itself has changed, manually refresh on your workstation then re-upload and see how the refresh goes there again.