Category: Custom Visuals

Diagnosing Power BI DirectQuery Performance Problems Caused By SQL Queries That Return Large Resultsets

One very common cause of Power BI performance problems is having a table with a large number of rows on your report. It’s a problem I wrote about here, and while I used an Import mode for my example in that post I also mentioned that this can be an even bigger problem in DirectQuery mode: while the DAX query for the table visual might have a TOPN filter that asks for 502 rows, the query going back to the DirectQuery data source (usually a SQL query) may not have that filter applied and could return a much larger number of rows, which could then take a long time for Power BI to read. I wrote about this in more detail here and showed how you can diagnose the problem in Performance Analyzer by looking at the Execute DirectQuery event and ActualQueryDuration, RowsRead and DataReadDuration metrics. But now I have a custom visual to display Performance Analyzer export data, what does this look like? Also, what can Execution Metrics tell us?

Using a simple DirectQuery model built from the ContosoDW SQL Server sample:

…I built a report with a table visual whose DAX query triggered a single SQL query that returned 475038 rows:

Here’s what a Profiler trace that included the Execution Metrics event showed me:

Some things to notice here:

  • The DAX query takes 3.5 seconds, as seen in the Duration column for the Query End event and the durationMs Execution Metric
  • The DirectQuery End event has a Duration of 2 seconds, leaving a gap of 1.5 seconds that needs to be explained
  • This Duration of 2 seconds for the DirectQuery End event matches to the externalQueryExecutionTimeMs Exection Metric, which is 2.054 seconds, but the docs only say that this is the “Total time spent on executing all external datasource queries during the request” which is a bit vague
  • The actual explanation for the gap comes from the directQueryIterationTimeMs Execution Metric which is 1.1 seconds, although this is still 0.4 seconds short of the 1.5 second gap mentioned above
  • The directQueryTotalRows Execution Metric shows that 475038 rows were returned by the SQL query
  • Execution Metrics provide an aggregated summary of metrics at the DAX query level; in this case there is only one SQL query generated but if (as is often the case) there was more than one, it would be hard to know what each SQL query was contributing to the problem

Here’s what my custom visual showed with data from Performance Analyzer for the same DAX query:

Now here’s the same visual with the tooltip from the Execute Direct Query event which shows some of the metrics associated with that event, shown:

This shows something very similar to what the Execution Metrics event in Profiler showed:

  • The Execute DAX Query event has a duration of 3.5 seconds
  • The Execute Direct Query event has a duration of 2.1 seconds – meaning that again there is a gap to be explained, a gap where no activity seems to be taking place in the visual (clearly visible in the first of the two screenshots immediately above – the tooltip obscures this gap)
  • As mentioned in my older blog post, this gap is explained by the DataReadDuration metric (documented here) from the Execute Direct Query event – which, as shown in the tooltip in the screenshot above, is 1.1 seconds
  • The amount of time it takes to read all the data from a large resultset can only be measured from the client (ie Power BI) side – a lot of customers I work with measure SQL query performance on the server side and see fast queries, but a fast query that returns a large number of rows that all need to be sent to Power BI can of course be slow
  • The ActualQueryDuration metric, also shown in the tooltip, gives you the amount of time it took to get the first row back from the SQL query
  • Unlike the Execution Metrics Profiler event, this DataReadDuration metric is available for each SQL query generated by a DAX query, which means you can tell exactly which SQL query/queries are causing problems

What can we learn from this? Apart from the fact that table visuals with vertical scrollbars can be a really bad thing, any time you have a DirectQuery model that generates SQL queries that return a very large number of rows, you could be paying a very heavy price to read all those rows – especially if you are getting close to the Max Intermediate Row Set Count limit, which is set to 1 million rows by default. What can you do about this? Apart from redesigning your report, I blogged about a technique here where aggregations can help for scenarios involving degenerate dimensions; using the new calendar-based time intelligence feature can also help to reduce the number of rows returned by SQL queries, as I described here. In general you’ll have to try to tune the DAX in your measures and your model to see what you can do to optimise the SQL queries Power BI generates so they return fewer rows.

Visualising Power BI Performance Analyzer Data With A Vibe-Coded Custom Visual

Performance Analyzer is now available in the browser, not just in Power BI Desktop! Actually everyone got excited about this back in September when it was announced and then forgot about it because it didn’t appear immediately, but now if you are in Edit mode for a published report you can see Performance Analyzer is there in the View menu. Why should you care though? Personally, while I’ve used Performance Analyzer in Desktop often enough over the years to capture DAX queries for performance tuning but I’ve always used DAX Studio, Profiler and Workspace Monitoring/Log Analytics for most of my performance tuning work. In part this is because Performance Analyzer was only available in Desktop – and the performance and behaviour of Power BI reports can be substantially different in Desktop compared to when they are published, and published reports are what your users care about. Now that Performance Analyzer is available for published reports I thought it was time to take another look at it, specifically at the detailed information it gives you when you export its data to json (which is documented here), and write a series of posts on when it can be useful for troubleshooting performance issues.

It’s very easy to use Power Query to load this export data into Excel or Power BI for analysis, so easy it wasn’t ever worth writing a blog post about it. Visualising this data is another matter because none of the Power BI native visuals are suited to the problem and indeed I was never able to find a custom visual that did the job satisfactorily either; but visualising this data is essential to understanding it properly because of the parent/child relationships between events. I really needed to build my own custom visual or use a tool like Deneb to do the job, but I didn’t have the time or skills to do so. However, a few months ago Phil Seamark showed me how to use GitHub Copilot to create custom visuals (see his blog here) and after a few hours of playing around I had something I was happy with.

You can download a sample pbix file with a Power Query query that extracts the data from a Performance Analyzer json export file and visualises it with my custom visual here. I’m not going to publish the code for any of this officially (at least not yet) because it’s still very much a rough draft; as I write more posts in this series I’ll know I’ll need to fix bugs and add functionality to the M code and the visual so things will change a lot. There are also some quirks in the data I need to understand better. Why am I writing this post if I’m not sharing the code, you may ask? I want to explain how I’m visualising Performance Analyzer data when I show screenshots of me doing so in future posts in this series. The point of the series will be to troubleshoot performance problems; the fact I’m doing so using a hacky, vibe-coded custom visual is important for context but it’s not the main aim. It’s also a great example of how AI enables a completely new type of workflow and allows someone like me to do stuff I couldn’t do before.

To illustrate what I’ve got so far, I built a report from an Import mode model with a slicer, a line chart, a scatter plot and an Azure Map visual then recorded events in Performance Analyzer in the browser when I changed the slicer selection:

After exporting the Performance Analyzer data to json and importing that data into my pbix file, here’s what my custom visual showed:

The whole interaction took around 0.7 seconds; scrolling down shows the events for each visual grouped together with the first event for a visual being its Visual Container Lifecycle event and other events relating to things like rendering and queries being run displayed underneath. A grey horizontal line marks the end of a visual’s events. I noticed that in this case some of the events associated with a visual seem to take place after the associated Visual Container Lifecycle event has finished and I think this is because these are events that are executed on different physical machines which may have clocks that are slightly out of sync – something that is called out in the docs.

I also used tooltips in the custom visual to display information for specific events like the DAX query:

That’s enough for now. In the next posts in this series I’ll show an example of how you can use Performance Analyzer and this visual to help troubleshoot specific problems.