Troubleshooting slow reports in PowerBI Report Server.

Troubleshooting “slow” reports in PowerBI Report Server (or SQL Server Reporting Services) can be an arduous task. End users are often unable to provide detailed (or reliable) data that a report took longer to load today than it did the last time it was run. Even if a user states that the report is now taking 10 seconds longer to load, that additional time needs to be attributed to a specific step in the report generation process before it can be improved/fixed.

In the report server database (ReportServer by default) there is a view (ExecutionLog) that can provide detailed statistics about each execution of a report. Note: ExecutionLog3 view is the newest/current version and the ExecutionLog and ExecutionLog2 views are for backwards compatibility. By default the execution log entries are retained for 60 days.

To configure report execution logging:

  1. Connect to the report server instance with SQL Server Management Studio.
  2. Right-click on the instance name and select Properties
  3. On the Logging page:
    • Select (or deselect) Enable report execution logging.
  4. On the Advanced page select the User-defined section:
    • ExecutionLogLevel can be set to verbose or normal.

After logging has been enabled, the report execution information can be retrieved with a simple Select query.

  • Last 10 executions of a specific report
SELECT TOP(10) *
FROM [ReportServer].[dbo].[ExecutionLog3]
WHERE [ItemPath] = <report path>
ORDER BY [TimeStart]
  • Last 10 actions by a specific user
SELECT TOP(10) *
FROM [ReportServer].[dbo].[ExecutionLog3]
WHERE [Username] = <login>
ORDER BY [TimeStart]

For easy access, the execution log can also be added to a PowerBI dashboard with slicers for easy filtering.

ExecutionLog3 View Details

Field NameDescription
InstanceNameName of the server and instance that executed the report request.
ItemPathPath to the report in the Report Server web portal.
UsernameName of the account executing the report.
ParametersReport parameters used in the report execution.
ItemActionRender, Sort, Execute, etc.
TimeStartDate and time that report processing began.
TimeEndDate and time that report processing completed.
TimeDataRetrievalNumber of milliseconds that it took to retrieve information from the database.
TimeProcessingThe number of milliseconds it took to process the report.
TimeRenderingThe number of milliseconds it took to render the report.
StatusEither rsSuccess or the first error code (if multiple errors occur).
ByteCountSize of the rendered report.
RowCountThe number of rows returned by the report query.
AdditionalInfoXML structure containing additional information about the report execution. Contents can vary between reports & executions. It may contain information like Estimated Memory Usage and the type of data sources.

1 thought on “Troubleshooting slow reports in PowerBI Report Server.”

Leave a Reply

Your email address will not be published.