Forum Discussion
Dataflow gen2 high CU usage
One of my gen2 dataflows uses relatively too much (i think) CU's for what it does:
I take 3 tables from warehouse, check max date and filter each of another 3 tables from on prem sql server source by that date (1 query from warehouse filters 1 from sql, query folding works) - destination is warehouse, append method. Last refresh, from one of tables with 4000 rows (10 columns) write to data destination part took 28mins, another table in same dataflow - 40k rows, write to destination - 15 seconds. Other datflows with 100k rows goes with no problems. That one dataflow used 85k CU's, and i would need higher capacity sku just because of that one dataflow after trial will end.
But it is not consistent, in last 7 days, 3 refreshes were witim 2 minutes time, others - around 30min, sometimes write to destination part, sometimes - looks like read part is 30mins. Any advices what to look for, what could cause this?
13 Replies
Hi matkvaid
Thanks for using Fabric Community.Here are some suggestions for what you can look for:
Dataflow Specifics:
- Query complexity: Analyze the queries closely. Are there any expensive operations like nested loops or large joins? Could the queries be optimized to improve efficiency?
- Data volume and schema: Even though the table sizes are small, consider the complexity of the data schema. Complex data types or nested structures can impact processing time.
- External systems: Investigate the on-premises SQL server and the warehouse. Are there any performance bottlenecks on those systems contributing to the delays?
General Troubleshooting:
- Test refreshes: Manually initiate dataflow refreshes and observe the behavior. This can help isolate specific triggers for slow performance.
- Log files: Analyze the dataflow logs for any error messages or performance warnings. These can provide valuable insights into the issue.
If none of the above suggestions provide a clear answer, consider contacting Microsoft Fabric support. They have access to detailed diagnostic tools and can help pinpoint the root cause of the problem.
Hope this helps. Please let me know if you have any further questions.Sad thing that there is no transformations in power query, only one filter for date and it has query folding. I have noticed, that it is not one dataflow that has this problem, but it is totaly random. I have made a pipeline, where one dataflow is run after another is completed and any random can cause much hihger CU usage than it was on another days. So for now dataflows gen2 are not stable. We are waiting for on premises gateway support, hoping that it would stabilise situation and we could really plan what capacity we need to order.
Hi matkvaid
Apologies for the issue you have been facing. I appreciate your patience and understanding.I request you to please create a support ticket as this would help our engineering team to take a close look at your problem and help you better. You can create a support ticket here:
Microsoft Fabric Support and Status | Microsoft Fabric
You can also provide your feedback to us. Appreciate if you could share the feedback on our feedback channel. Which would be open for the user community to upvote & comment on. This allows our product teams to effectively prioritize your request against our existing feature backlog and gives insight into the potential impact of implementing the suggested feature. Hope this helps. Please let me know if you have any further queries.
My situation is not quite the same but what I am seeing is that after refreshing, usage remains high even though no one is doing anything. I suspect the back end resource recovery is not working correctly or optimally.
I am using about 10% of trial capacity, what should be equal to F64. And fabric still shows "usage" also when nothing runs - what i understand, this is just how it works. I hope that i am not wrong here with my math, but this is how i am planing for capacity purchase: 4 CUs * 60s * 60min * 24hrs is 345 600 CUs total, so my daily workloads have to not exceed that. By the way, You can edit capacity metrics app in service, i added my custom date slicer so i could monitor one day usage
