This post is a follow-up to my recent post on identifying CPU and memory-intensive Power Query queries in Power BI. In that post I pointed out that Profiler and Log Analytics now gives you information on the CPU and memory used by an individual Power Query query when importing data into Power BI. What I didn’t notice when I wrote that post is that there is also now information available in Profiler and Log Analytics that tells you about peak memory and CPU usage across all Power Query queries for a single refresh in the Power BI Service, as well as memory usage for the refresh as a whole.
Using the same dataset from my previous post, I ran a Profiler trace on the workspace and captured the Command Begin and Command End events while I refreshed the dataset. Here’s what Profiler shows for the Command End event:
In the upper pane, the Duration column tells you how long the refresh took in milliseconds and the CPUTime column tells you how much CPU was used by both the Analysis Services engine and the Power Query engine during the refresh. This is not new, and I wrote about the CPUTime column last year here.
In the lower pane where the TMSL for the refresh operation is shown – this is the text from the TextData column – underneath the TMSL there are three new values shown:
- PeakMemory shows the maximum amount of memory used during the refresh operation. This includes memory used by the Analysis Services engine and the Power Query engine.
- MashupCPUTime shows the total amount of CPU used by the Power Query engine for all Power Query queries used to import data into the dataset. This value will always be less than the value shown in the CPUTime column in the upper pane.
- MashupPeakMemory shows the maximum amount of memory used during the refresh operation by just the Power Query engine across all query evaluations. It’s important to note that this value may not be entirely accurate since the memory usage values, and therefore the peaks, are captured asynchronously so there could be higher peaks that are not detected.
This new information should be extremely useful for troubleshooting refresh failures that are caused by excessive memory consumption.
[Thanks to Akshai Mirchandani for this information]
Thank you for this.
When I have memory dataset failures, trying see exactly what queries and how much Memory they are consuming is my main issue. Have you found any way to see detail beyond the totals?
Thank you for the How-To. I wanted to use the PowerBI Log Analytics workspace to investigate sizes of refreshes, unfortunately there seems to be a size limit on the EventText-field ( 16kb limit on this field). Thus, for larger models the EventText is being truncated and the Memory fields are not accessible.
Does setting NoTruncate=true on the connector stop this happening? https://learn.microsoft.com/en-us/power-query/connectors/azure-data-explorer#configuring-azure-data-explorer-connector-options-in-an-m-query