Refresh suddenly takes 5-10x as long as normal

[JessicaRohrer]

I have a handful of artifacts that are taking 5-10x the normal run time all of a sudden. They never complete; I am cancelling before they kill the capacity. These are things that have been running for years, multiple times per day, without issue. There have not been recent changes. They are almost all dataflows but I've seen this today with one semantic model as well. Something that normally refreshes in 10 seconds takes a minute. Something that takes 10-12 minutes to refresh, I'm killing at an hour. This has been going on for about 6 hours. There are also similar models and dataflows using the same data sources, also running for years, that have absolutely no issue. I've poured over the capacity usage metrics and monitoring hub and nothing is standing out to me as an error. Below are the notes & commonality I can identify. As I mentioned it is only a handful of items experiencing issues, but this is very painful and I can't for the life of me pinpoint the issue.

- Dataflows are all gen 1

- All items have SQL data sources, SharePoint connector, as well as PowerPlatform connector to pull in other dataflows.

- SQL queries via SSMS and PowerBI Desktop load without any issue or lag time.

- I've confirmed all connectors are online and credentials are not throwing any errors.

- I've restarted the gateway a dozen+ times.

- Opening the dataflows and watching the preview load works without any issue. Previews load perfectly fine. I can also run row counts meaning it is able to evaluate the entire query past just the preview.

- I have ran monitoring queries in SSMS on the SQL database being queried to see if the query being pulled by the Dataflow was having a hard time processing. It is not there. This has also never happened to me. It almost seems as though the dataflow refresh icon is just spinning in the service but not actually querying.

- The dataflows do not show up under list of In Progress in the monitor tab of PowerBI service. I have never seen this before either.  They do not show up as running/in progress. This also leads me to believe they aren't actually running for an hour, but rather idling.

Has anyone seen anything like this before?

UPDATE: In an attempt to see an error code, I decided to let one run even though the run times are terrifying. I troubleshooted one dataflow that normally runs in 2-3 minutes, I let it run forever to see what error message returned. It completed in 58 minutes with no error. I opened the dataflow and copied/pasted the queries into a PBIX desktop file. It loaded in 2 minutes. I copied/pasted the queries into a new dataflow in a pro workspace off of the premium capacity. It is the same issue, just spinning forever. 

[JessicaRohrer]

UPDATE: After a long, sleepless night of troubleshooting, I took one of the dataflows that has both PowerPlatformDataflows connector and SQL connector, and replaced the dataflow being referenced with a flat table (enter data method). The dataflow that was taking 35+ minutes to load instead of the normal 1 minute, loaded in 45 seconds. I tried to replace the PowerPlatformDataflows connector with the legacy PowerBI connector to see if that might help, as it has helped me in the past, and it did not help. 

I am also still not seeing any queries trying to run in the Monitor. It is like I can't connect to PowerBI service, and things are just idling, but I can connect to SQL and other outside sources.

My org did turn on autoscale yesterday but that should not be impacting anything. However, due to the issue I am experiencing, they turned autoscale back off just to rule it out while we figure this out.

Any ideas or input are greatly appreciated!!

[JessicaRohrer]

At approximately 1:15PM, nearly 24 hours after the issue began, it stopped. Everything returned to normal functionality with our entire IT team and myself puzzled. We did not change anything. It would seem there was an outage of some kind that we were not made aware of. I've searched and searched and cant find anything to support an outage, but it almost behaved like the capacity was over 100% - I have seen this take 24 hours to return to normal. However, the capacity has been at 6-7% for the past few days.

If anyone happens to find this thread and has relevant feedback, I'd be glad to hear it!! Thanks for reading if you made it this far 🙂

[v-xiandat-msft]

Hi @JessicaRohrer ,

Direct Query for Dataflows: If you’re dealing with large datasets, consider using direct query for dataflows. This approach can improve performance, especially when combined with the creation of aggregation tables in your model
Clear Cache in Power BI Desktop: Clearing the cache can sometimes resolve issues with slow loading. If the problem persists, it could be related to a routing issue or a corporate firewall
Performance Analyzer Tool: Use the performance analyzer tool in Power BI Desktop to identify any bottlenecks in report performance

Below is the link will help you:

Solved: Power BI dataflow very slow - Microsoft Fabric Community

Best Regards,

Xianda Tang

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

[JessicaRohrer]

Hi there. Thanks for the response but this solution does not make any sense. DirectQuery is not an option for dataflows and has nothing to do with the issue I am experiencing. I also mentioned that there is no issue in PowerBI Desktop and it was isolated to the Service, so I'm unsure how clearing cache in PowerBI Desktop would help, although this is a regular practice. I clear my cache daily, sometimes multiple times per day because I often run into memory issues.