Dataflow gen2 high CU usage

[matkvaid]

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?

[ebjim]

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.

[matkvaid]

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

[v-nikhilan-msft]

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.

[matkvaid]

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. 

[v-nikhilan-msft]

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.

[v-nikhilan-msft]

Hi @matkvaid 
We haven’t heard from you on the last response and was just checking back to see if you got a chance to create a support ticket. If yes please share the details here.
Otherwise, will respond back with the more details and we will try to help.
Thanks

[matkvaid]

Yes, i have creted a ticket and have no solution about the case for now

[v-nikhilan-msft]

Hi @matkvaid 
Please provide the support ticket number. I can track it further and provide the details.

[matkvaid]

2401310050005351

[matkvaid]

Well what can i say - the support is useless. I have a ticket for this, open for a month. For all this time support have done nothing and provided no information. Just try to call whenever they want, ignoring time zones. I now have better situation after i found in this forum, that on premise gateway update - helps. But i got this not from support. So i can say just one thing - microsoft support, especially mindtree limited - is useles incompetent time wasters.

[gabriel-gc]

exacly, i have a ticket opened with already around 50 emails, 20 of them is explaining over and over again the problem, that is quite simple (the visuals does not load in certain times). I'm carrying this ticket to see where it goes, looks like they win getting us tired to solve the problem.

[matkvaid]

I am amazed, how it can be bad like that. I had another ticket open, in two weeks i found how to solve the problem myself. In that time support just asked same questions in emails and teams calls. I had another issue with UTC times and on prem data gateway. It was a bug, i was ignored in support, here and in official reddit sub, no answers were given... I am happy that the bug was fixed, but - i had made workarounds for my reports to work, and the fix broke them. I got questions from report users, why the reports do not work AGAIN... In my opinion, Fabric is still far from being ready for production...

[JustinWesty]

Hi Matkvaid,

What I have found on random occasions is that when I have a comment line in my M code, the query doesn't fold (look at your process volume in the output; I would assume the volume is unfolded). So, I downloaded a template of my original and removed all comments.

example 

#"Filtered rows 1" = Table.SelectRows(#"Removed other columns", each [entryNo] > _maxEntryNo),

//#"Filtered rows 1" = Table.SelectRows(#"Removed other columns", each [entryNo] > 0)

Fix

#"Filtered rows 1" = Table.SelectRows(#"Removed other columns", each [entryNo] > _maxEntryNo),