In this blog post below, I am going to show you how to configure the Load Balancing with CPU and Memory.

I always recommend for On-Premise Data Gateway installations that there be at least 2 Gateways installed.

The initial reason was to ensure that if one gateway went down the other one would be able to still refresh or connect to the DirectQuery or LiveConnection sources.

With the recent update where you can now control the CPU and Memory on each Gateway instance, this means that I am able to define a dedicated server to take more of the refreshing/DirectQuery/LiveConnection load. And I can offload secondary refreshing to the second server which might be installed on another server with multiple roles.

NOTE: In order this to work you must have the Oct 2019 version of the On-Premise Data Gateway installed.

Description for Aggregation Time Period

The configuration item is how long should the aggregation period be?

What this means is how many minutes should the CPU or Memory system counters be aggregated to compare against the configuration items configured below.

The value is defined as number and represents minutes.

For example: The default is 5, meaning 5 minutes.

  • It then takes the CPU Utilization over the past 5 minutes and aggregates this to get an average value over the last 5 minutes.

Description Details for CPU Threshold

In order to configure the CPU threshold limits it is good to understand how it will be applied, which I explain below.

  • The values you can use are between 0 and 100 representing the % of CPU utilization across all the CPUs
  • Which means the following:
    • 0 – configuration is disable. Or it can use all the CPU
    • (1-100) – when the throttling will be applied.
  • For example, if I set the number to 60
    • It will then throttle the refreshing on this server when the % CPU Utilization gets to 60%

Description Details for Memory Threshold

In order to configure the Memory threshold limits it is good to understand how it will be applied, which I explain below.

  • The values you can use are between 0 and 100 representing the % of Memory utilization
  • Which means the following:
    • 0 – configuration is disable. Or it can use all the Memory
    • (1-100) – when the throttling will be applied.
  • For example, if I set the number to 40
    • It will then throttle the refreshing on this server when the % Memory Utilization gets to 40%

Changing the configuration values

Next is where I log into my server and change the configuration values.

  • In my example I am going to apply the following throttling values
    • Resource Utilization Aggregation Period = 4
    • CPU Utilization = 60
    • Memory Utilization = 70
  • It is recommended to always make a backup or copy of the file before working on it, if something is wrong it is easy and simple enough to roll back.
    • I make a copy of the current file 
      "Microsoft.PowerBI.DataMovement.Pipeline.GatewayCore.dll.config"
  • I open the following configuration file:
    C:\Program Files\On-premises data gateway\Microsoft.PowerBI.DataMovement.Pipeline.GatewayCore.dll.config
  • I find the section where I can modify the settings, it should be around line 109 – 117
    • I did notice that the spelling of Aggregation is slightly different, but we are only changing the settings so there is no issue there!
    • This is the default settings
  • Next is where I update my values as explained above.
  • I save and close the file.
  • The final step is to restart the On-Premise data gateway service
  • This then completes the configuration changes.

Recommendation for settings

What I would recommend is to always have one Gateway server where it can run at 100% of CPU Utilization and 100% of Memory Utilization.

If all members in the cluster are at their throttling limit the requests will fail.

Conclusion

I hope that this has been useful and that it can ensure that your On-Premise Data Gateways can perform and keep on refreshing your data.

Here is a link to the blog post from Microsoft on the details: On-premises data gateway October 2019 update is now available

Thanks for reading and any comments or suggestions are most welcome!