Dataset Refresh fluctuations

[cassidy]

Looking for insights into the reasons a Dataset Refresh runtime could fluctuate signficantly in Service

A few facts:

1. No published changes to the Dataset during the period
2. Capacity has remained under 50% Usage during the period
3. Refreshes in Desktop are very consistent, leading me to believe it's a Service issue rather than a Query/Performance issue.

It's a large Dataset and it's typically refresh time is 1-1.5 hours, which is acceptable.

Notice how the Refresh times trend upwards finally hitting 3 Hours for the same exact Refresh that took less than 1 Hour just 2 days prior!

Utilization during this period was very resonable:

Over the years, I've run many traces to further improve performance, but I don't see how inconsistent Service Refresh times are something I can control.

Thoughts?

[cassidy]

I got my Trace back from the last 3 hour refresh, pretty wild result:

The tables causing the slow down were simple few hundred row Excel file connections (Web Connector using sharepoint link location).

These Excel tables take around 20 Seconds typically in Service (and even less Locally), and are now taking upwards of 60-90 Minutes each!

Every other table (nearly all Dataflows) returned in a normal amount of time.

[GilbertQ]

Hi @cassidy 

Yeah sometimes what happens is that there might be challenges and issues in the background which we are not aware of. I would suggest seeing if it is still like this in a few days time?

Hopefully it should resolve itself. I have seen things auto resolve in the past!

[cassidy]

The refreshes walked themselves back down from 3 hours to 1 hour without me doing anything

To your point, sometimes these things "fix" themselves.  It's one of the unfortunate sides of Power BI in that the Admin side is very "light", so much is being done that cannot be seen.  I don't doubt there was an important reason my Dataset tripled in refresh time...but why?  How am I supposed to explain these instances to our Users?

Not expecting an answer, just calling it out

[GilbertQ]

Hi @cassidy 

Yeah this can happen from time to time, but it should not be very often.

[GilbertQ]

Hi @cassidy 

I am not sure why it would take longer, I would suggest what is the memory usage like?

And are you running on Gen2 capacity which should make things run faster?

[cassidy]

@GilbertQ It's a P1 Capacity on Gen2.

Do you have tips on viewing Memory Usage?  The Capacity Metrics only show CPU.

I do have consistenly fast times on my local machine (64GB RAM), I can see how maybe the Capacity might be struggling at 25GB, but I don't get any Memory failures and I don't know of a way to monitor Memory usage in real time.