topic Re: Dataflow gen2 high CU usage in Dataflow

Dataflow gen2 high CU usage

matkvaid — Mon, 29 Jan 2024 12:48:00 GMT

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?

Re: Dataflow gen2 high CU usage

Anonymous — Tue, 30 Jan 2024 10:34:41 GMT

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.

Re: Dataflow gen2 high CU usage

matkvaid — Tue, 30 Jan 2024 10:39:39 GMT

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.

Re: Dataflow gen2 high CU usage

Anonymous — Wed, 31 Jan 2024 16:58:45 GMT

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.

Re: Dataflow gen2 high CU usage

Anonymous — Fri, 02 Feb 2024 18:58:29 GMT

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

Re: Dataflow gen2 high CU usage

matkvaid — Fri, 02 Feb 2024 19:05:26 GMT

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

Re: Dataflow gen2 high CU usage

Anonymous — Fri, 02 Feb 2024 19:06:58 GMT

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

Re: Dataflow gen2 high CU usage

ebjim — Fri, 02 Feb 2024 19:45:52 GMT

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.

Re: Dataflow gen2 high CU usage

matkvaid — Fri, 02 Feb 2024 19:49:17 GMT

2401310050005351

Re: Dataflow gen2 high CU usage

matkvaid — Fri, 02 Feb 2024 19:55:25 GMT

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

Re: Dataflow gen2 high CU usage

matkvaid — Sat, 02 Mar 2024 14:48:33 GMT

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.

Re: Dataflow gen2 high CU usage

Anonymous — Thu, 18 Jul 2024 13:02:55 GMT

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.

Re: Dataflow gen2 high CU usage

matkvaid — Fri, 19 Jul 2024 10:24:55 GMT

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...

Re: Dataflow gen2 high CU usage

JustinWesty — Tue, 30 Jul 2024 00:49:36 GMT

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),