Edit

Monitor report performance in Power BI

  • Article

Monitor report performance in Power BI Desktop using the Performance Analyzer. Monitoring will help you learn where the bottlenecks are, and how you can improve report performance.

Monitoring performance is relevant in the following situations:

  • Your Import data model refresh is slow.
  • Your DirectQuery or Live Connection reports are slow.
  • Your model calculations are slow.

Slow queries or report visuals should be a focal point of continued optimization.

Note

The Performance Analyzer cannot be used to monitor Premium Per User (PPU) activities or capacity.

Use Query Diagnostics

Use Query Diagnostics in Power BI Desktop to determine what Power Query is doing when previewing or applying queries. Further, use the Diagnose Step function to record detailed evaluation information for each query step. The results are made available in a Power Query, and you can apply transformations to better understand query execution.

Screenshot of Power Query Editor Tools ribbon tab showing the Diagnose Step command, Start Diagnostics command, and the Stop Diagnostics command.

Use Performance Analyzer

Use Performance Analyzer in Power BI Desktop to find out how each of your report elements—such as visuals and DAX formulas—are doing. It's especially useful to determine whether it's the query or visual rendering that's contributing to performance issues.

Use SQL Server Profiler

You can also use SQL Server Profiler to identify queries that are slow.

Note

SQL Server Profiler is available as part of SQL Server Management Studio.

Use SQL Server Profiler when your data source is either:

  • SQL Server
  • SQL Server Analysis Services
  • Azure Analysis Services

Caution

Power BI Desktop supports connecting to a diagnostics port. The diagnostic port allows for other tools to make connections to perform traces for diagnostic purposes. Making any changes to the Power Desktop data model is supported only for specific operations. Other changes to the data model with operations that aren't supported may lead to corruption and data loss.

To create a SQL Server Profiler trace, follow these instructions:

  1. Open your Power BI Desktop report (so it will be easy to locate the port in the next step, close any other open reports).
  2. To determine the port being used by Power BI Desktop, in PowerShell (with administrator privileges), or at the Command Prompt, enter the following command: 
    netstat -b -n
    The output will be a list of applications and their open ports. Look for the port used by msmdsrv.exe, and record it for later use. It's your instance of Power BI Desktop.
  3. To connect SQL Server Profiler to your Power BI Desktop report:
    1. Open SQL Server Profiler.
    2. In SQL Server Profiler, on the File menu, select New Trace.
    3. For Server Type, select Analysis Services.
    4. For Server Name, enter localhost:[port recorded earlier].
    5. Click Run—now the SQL Server Profiler trace is live, and is actively profiling Power BI Desktop queries.
  4. As Power BI Desktop queries are executed, you'll see their respective durations and CPU times. Depending on the data source type, you may see other events indicating how the query was executed. Using this information, you can determine which queries are the bottlenecks.

A benefit of using SQL Server Profiler is that it's possible to save a SQL Server (relational) database trace. The trace can become an input to the Database Engine Tuning Advisor. This way, you can receive recommendations on how to tune your data source.

Monitor Premium metrics

Monitor performance of content deployed into your organization's Power BI Premium capacity with the help of the Microsoft Fabric Capacity Metrics app.

Related content

For more information about this article, check out the following resources: