Refresh timeouts - No load on Gateway or SQL DB

[MoorsTech]

Hello Everyone,

we have recently migrated our SQL and Enterprise Gateway servers and are experiencing strange and intermittent timeout problems with Power BI service refreshes of datasets.

My main concern is that neither the gateway nor the SQL server are under almost any load. As I was investigating this behaviour, I have triggered 200+ dataset refreshes simultaneously via the API (which should process 2 billion+ rows in aggregate), but the maximum CPU load on the gateway was only ~30% for a couple minutes, averaging around 15%. The SQL database itself never crossed 10%. At the same time, the service refreshes take a long time to run and even time out in some cases. Desktop refresh of the same datasets run just fine.
When observing the task manager during the tests, it seems there are only ~4 concurrent processes causing load on the system, making me suspicious of the concurrency settings of the gateway. I have however already enabled the StreamBeforeRequestCompletes option and increased the MashupDefaultPoolContainerMaxCount (as described here) setting to 80 in the gateway configuration which should be way too much for this CPU to handle and the load behaviour still did not change. See screenshot:

Here the hardware specs (identical for gateway and SQL database):
- Ryzen 5950X (16 core)

- 128 GB RAM

- Dedicated 10gbit LAN connection between the gateway and SQL server

I'd appreciate any hints on what to do here 😃

Cheers

[MoorsTech]

The primary error message is:
"Before the data import for finished, its data source timed out. Double-check whether that data source can process import queries, and if it can, try again." and sometimes we also get the "Power BI may have lost connectivity with the data gateway during this dataset refresh. Please make sure that the data gateway is up and running for the next scheduled refresh." error

I am confident that its not a problem with the SQL database as there is barely any activity on it. At most 2 queries are running simultaneously and as stated in my original post, almost no CPU load. Each individual dataset should easily have 10 simultaneously executing queries.
And yes, I did trigger the refreshes on different datasets for this test.
I should also point out that installing the gateway on a less powerful server actually solves the issue and is our current workaround. I'm also in contact with the support regarding this issue.

[lbendlin]

does the timeout error message wax on about "transient issues" and ask you to "try again later" ?

In my experience each dataset refresh will only take 2GB of memory on average.

Are you confident that your SQL server can handle all the spool requests?

Also for stress testing you need to issue refresh requests to different datasets.  If you ask for the same dataset to be refreshed over and over these requests will be ignored (especially while a refresh is in progress). And Power BI Service might try to outsmart you with its cache management.