Monitor report performance in Power BI
Monitor report performance in Power BI Desktop by using the Power BI Premium Metrics app, learn where the bottlenecks are, and learn 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.
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.
Query Diagnostics is currently a preview feature, and so you must enable it in Options and Settings. Once enabled, its commands are available in the Power Query Editor window, on the Tools ribbon tab.
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.
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
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 not supported. Changes to the data model may lead to corruption and data loss.
To create a SQL Server Profiler trace, follow these instructions:
- Open your Power BI Desktop report (so it will be easy to locate the port in the next step, close any other open reports).
- To determine the port being used by Power BI Desktop, in PowerShell (with administrator privileges), or at the Command Prompt, enter the following command:
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.
netstat -b -n
- To connect SQL Server Profiler to your Power BI Desktop report:
- Open SQL Server Profiler.
- In SQL Server Profiler, on the File menu, select New Trace.
- For Server Type, select Analysis Services.
- For Server Name, enter localhost:[port recorded earlier].
- Click Run—now the SQL Server Profiler trace is live, and is actively profiling Power BI Desktop queries.
- 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
For Power BI Premium capacities, use the Power BI Premium Metrics app to monitor the health and capacity of your Power BI Premium subscription. For more information, see Power BI Premium Metrics app.
For more information about this article, check out the following resources: