Reduce concurrent refreshes in Service

[Anonymous]

Hello,

  In the desktop, I set the maximum number of simultaneous evaluations to 2. I refreshed the project, from my desktop, and my DBA used SQL Server Manamgement Studio and verified that only 2 of my queries were running at a time. I published this to my test workspace. I then went into the Power BI service and triggered a manual refresh on the published datasource. Again, my DBA monitored the number of executing queries. this time, I had between 5 and 6 concurrent queries running; eventhough I had set the number in my desktop settings. Where do I set this number in the PowerBI service?

Cheers!

Peter

[edhans]

You cannot. You might look at the tuning the performance of the gateway starting here and looking at these pages. Use the on-premises data gateway app | Microsoft Docs

Those desktop settings though are to help developers when they are working on a lower end machine, or don't want to adversly impact the servers during development when refreshes might be rapid and often.

It is assumed for the service it needs to get the data as fast as possible to lessen the load on the service itself and will use as much resources as necessary to accomplish that goal.

[Anonymous]

Hello,

  Thank you for the fast reply. I will investigate the documentation link you provided. Can you make the requested changes if you upgrade to PowerBI Premium, perhaps an A1 level? Other than scheduling refreshes at different times, is there any other way to limit the load placed on the SQL server?

Thanks,

Peter

[edhans · Accepted solution]

No - premium doesn't give you more control that way. 

If your load on the SQL Server is that great from Power BI there are a number of options to try.

1. Create views in SQL to optimize the transformations and pull only what you need.
2. Elimiate any transformations folded back to the server using the Table.StopFolding function very early. This will genereally increase the data transmitted but reduce the CPU/RAM usage. Everything will happen on the gateway.
3. As you said, adjust the scheduled refresh times outside of normal or peak hours.