Another new metric has appeared in Profiler/Log Analytics recently, added to the end of the query text shown in the TextData column for the Query End event. It’s called WaitTime:
What does WaitTime represent? Here’s the technical explanation: it’s the wait time on the query thread pool in the Analysis Services engine before the query starts to run. But what does this mean for you as someone trying to tune DAX queries in Power BI?
As I explained recently in my post on load testing in Power BI, when you publish your dataset to the Power BI Service it runs on an instance of a version of the Analysis Services engine on a node somewhere inside the infrastructure that we at Microsoft manage for you. You don’t see any of this, but there’s still a machine with a limited amount of CPU and memory available and it can get overloaded if there are too many expensive queries running, or too many datasets being refreshed at the same time, or even one large dataset being refreshed; part of the magic of the Power BI Service is how we move datasets around to ensure this happens as infrequently as possible. If, however, you’re very unlucky you may run a query on a dataset that’s running on an overloaded machine and the performance of that query may be affected because the CPU is too busy. In that case you may see a value for WaitTime that is greater than zero and this means that your query was slower than it might otherwise have been.
This can happen for datasets in both Import mode and DirectQuery mode. It can happen because of something that’s happening on the dataset you’re querying – such as a load test or a full refresh – or it can happen because someone else is doing something similar with a dataset that happens to be on the same node as yours and the Power BI Service hasn’t managed to move your dataset to a quieter node yet. It is not the same thing as throttling in Premium (or “interactive request delays” as it’s officially called) but if you have a capacity that’s being throttled because of something you’re doing with a specific dataset then queries on that dataset may also have a WaitTime greater than zero.
I haven’t seen a non-zero value for WaitTime yet, but I only found out about it yesterday so I’m looking forward to seeing whether I see one next time I’m doing some performance tuning. If you see one let me know in the comments.
[Thanks once again to Akshai Mirchandani for this information]
Looks like I have something else I need to add to the Server Timings in DAX Studio…
Hello Chris, I’m looking for a way to find all the refreshes happening in the window on my premium capacity to determine the culprit. Could you suggest me .. TIA
You can use the tenant audit logs to collect these events – after the fact. There’s nothing you can do in real time as far as I am aware (the Premium Capacity Metrics app is rather inconclusive on this).
Have you looked at the Refresh Summaries page in the Admin Portal? https://learn.microsoft.com/en-us/power-bi/connect-data/refresh-summaries
A very new-admin question – what would the next steps be if essentially every DAX query in my datasets is running into long wait times? Is it a matter of optimizing queries? Hardware/CPU improvements? How can I use this information as evidence for an action I need to take?
All you can do is optimise your queries, your datasets and your reports – and even then a lot of factors are out of your control. But does every query actually have a long wait time?
Depends what counts as “long” I suppose. Looking at the dataset I used as a test case today for my first interaction with this process, I’m seeing 27 DAX Query Query End events. Of those, 14 had wait times of under a second. Only one had no wait time. Of the others, most are around 2 or 3 seconds, but some of them are 12, 14, even 23 seconds. For context, I ran this trace at a time when there was very little other traffic in our environment. We really have no systemic approach to query optimization right now and are just starting to think about it, several years after we started using PBI, so we have a lot of catching up to do and technical debt to clear that we didn’t even realize we had.
Actually, I was (probably obviously) misunderstanding what I was looking at. Disregard my panic, sorry.
Hi Chris, is there a way to send you a question about something unrelated to this blog’s topic ?
You can use the form on the Contact page. If you have a technical question please post it to a public forum and then send me the link to the post.