re using Power Method to throttle on premise gateway

[bruceandersonBI]

We are using Power BI service.   Our reports are pulling the data through the on prem gateway from SQL server.  The issue is it does impact our live ERP system when it is refreshing.  We have changed all the heavy reports to be incrmemntal refresh, but it is still impacting.  I wonder if there is a way to set a limitation on the number of records per second, or kb per second the gatway or BI pulls?  Or some other method to throttle it?

[v-jingzhang]

Hi @bruceandersonBI 

It's hard to say. How does it impact your ERP system? Does the ERP system also get data from the same SQL Server? Do you use on-demand refresh or scheduled refresh on Power BI Service when you see it impacts the ERP system?

I guess your datasets are in import mode. In my understanding, when the on-premises gateway is triggered to refresh the datasets, it will build connections to the on-premises data sources, get data from those data sources and temporarily store data on the gateway machine until all data is received from the data sources, then the data is sent back to the cloud service. During this process, the on-premises gateway will take up the CPU and memory resources of the machine it is based on.

So I suggest you may try setting schedule refresh on the datasets at an inactive time of the ERP system. You can also set different schedule time for different datasets to break down the refresh load on the gateway. Also, there may be connection limits or throughput limits on the SQL Server side which may influence this issue.

There are also some settings you may try on the gateway, please refer to the following links:

https://docs.microsoft.com/en-us/data-integration/gateway/service-gateway-performance-cpu

https://docs.microsoft.com/en-us/data-integration/gateway/service-gateway-high-availability-clusters

Best Regards,

Community Support Team _ Jing Zhang

If this post helps, please consider Accept it as the solution to help other members find it.

[bruceandersonBI]

Many thanks for the reply.  Much food for though you gave me.

I have already tried adjusting the times job run to about the best I can get them to limit the impact on the SQL server.  Many are requird to refresh during the working day so I can't avoid that.

And the issue is not with the machine that the gateway is on, but the SQL server and service on another machine that that it pulls from.  I was hoping for a "Records per minute" setting or the like but it looks like that doesn't exist.  I will check again for options on the SQL side.