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
|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.|