Forum Discussion
Advocate VRefreshes from SQL over the Gateway taking longer and longer
We have a Power BI Premium Capacity (yep, still thanks to our EA) and I've noticed that over the past year, data refreshes for Power BI reports published to the service are taking an average of 200 seconds longer than they did at the same time last year. I've done some work to try and spread the load out so we don't have so many refreshes happening concurrently, but this still seems like a really big change over the course of one year. Most of our refreshes hit our on-prem SQL datamart, so that means the refreshes use our two-node Power BI gateway setup. The biggest change in refresh times seemed to happen around from September through November 2024, when the average time zoomed up to around 190 seconds. Has anyone else seen anything like this? Has anyone found a solution? Anyone have thoughts as to what might be going on?
23 Replies
- ToddChitt
Super User
For cases like this, you have to ask yourself, "What changed?" Some thoughts to get you started:
- Amount of data?
- Power Query steps such that it no longer 'folds'? (Meaning it has to do some steps at the client level, not the SQL Server)
- SQL Indexing strategy?
- Network bandwidth?
Can you isolate the performance issue to one table in a model? What happens if you extract the SQL statement from Power Query and run that against the server directly?
motorayAppreciate the thoughts. This is across all reports in the tenant and we have over 300. They're all taking much longer to refresh than they did this time last year--from the ones doing the heavy lifting with lots of data, to the ones doing a few dozen rows. The change happened about the same time as we started getting lots of failure for refreshes going across the gateway around July 2024. We then reverted to the May 2024 gateway and didn't update again until October...and that's when we started getting longer and longer refresh times.
One thing to note: we did add the server names to the config file.
https://powerbi.microsoft.com/en-us/blog/on-premises-data-gateway-september-2024-release/
- v-menakakota
Community Support
Hi motoray ,
You saw some relief after rolling back to May 2024, so version differences matter. Try testing with the latest gateway version on a non-production node there may be performance improvements or fixes.
Even small delays between the gateway and your SQL Server can add up across 300+ reports.It’s worth checking for increased network latency or DNS resolution issues
If I misunderstand your needs or you still have problems on it, please feel free to let us know.
Best Regards,
Menaka.
Community Support Team
- Poojara_D12
Hi motoray
You're observing a significant increase in data refresh durations for Power BI reports published to your Premium Capacity environment—on average, about 200 seconds longer now compared to the same time last year. Despite efforts to balance the refresh load and avoid concurrency bottlenecks, performance hasn't improved much. Most of your reports pull data from an on-prem SQL datamart via a two-node Power BI gateway cluster, which adds a layer of complexity to troubleshooting. The most notable increase in refresh times occurred between September and November 2024, indicating a possible correlation with either a change in the Power BI service, gateway updates, or infrastructure changes in your data source environment. This kind of performance degradation could be due to several factors, including changes in Power BI’s internal refresh engine behavior (especially with Premium Gen2 optimization rollouts), increased dataset complexity, SQL server performance issues, gateway load contention, or even subtle changes like Windows patches or network latency that affect the gateway-to-database connection. It’s also possible that refresh performance telemetry or capacity metrics during peak times could reveal memory or CPU constraints on your Premium node or gateway machines. To investigate further, you might analyze metrics from the Power BI Premium Metrics App, Gateway Performance Monitoring logs, and refresh logs in the Service to identify bottlenecks. Solutions others have used include scaling gateway hardware, updating gateway software, isolating high-demand refreshes to separate time windows, or migrating the gateway closer to the database for lower latency. This is a common concern for Premium users with growing usage and complex on-prem data pipelines, and pinpointing the root cause usually involves coordinated monitoring across Power BI, the gateway, and the SQL datamart.
- motoray
There's a lot to look at here. We're going to start with adding another node to our gateway cluster.
