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:
- Connect to the report server instance with SQL Server Management Studio.
- Right-click on the instance name and select Properties
- On the Logging page:
- Select (or deselect) Enable report execution logging.
- 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 Name | Description |
| InstanceName | Name of the server and instance that executed the report request. |
| ItemPath | Path to the report in the Report Server web portal. |
| Username | Name of the account executing the report. |
| Parameters | Report parameters used in the report execution. |
| ItemAction | Render, Sort, Execute, etc. |
| TimeStart | Date and time that report processing began. |
| TimeEnd | Date and time that report processing completed. |
| TimeDataRetrieval | Number of milliseconds that it took to retrieve information from the database. |
| TimeProcessing | The number of milliseconds it took to process the report. |
| TimeRendering | The number of milliseconds it took to render the report. |
| Status | Either rsSuccess or the first error code (if multiple errors occur). |
| ByteCount | Size of the rendered report. |
| RowCount | The number of rows returned by the report query. |
| AdditionalInfo | XML 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.”