The Microsoft blog post here by Robert Bruckner is a great resource for helping get the most out of the SSRS execution log.
It was written for SSRS 2008’s ExecutionLog2 view, but is still relevant for ExecutionLog3 in SSRS 2012 and later, since the latter just renames 2 columns and adds 2 elements to the AdditionalInfo XML structure.
Of particular interest is Bruckner’s section on “Tips for Analyzing ExecutionLog2 Information”, where he gives many rules of thumb for what to look for in the log to help optimise performance of an SSRS installation. I’ve used this information to create a report that’s specifically tailored around his advice.
The report has parameters for a date range and for a report type, where there is one report type for each of Bruckner’s tips:
Here’s an example of one of the report type options:
Under the header I’ve included the paragraph that Bruckner wrote for each tip (with slight edits) so that the point of the report is clear, and then under that, the table for each report type has rows & columns that are pertinent for the tip.
The report dynamically configures itself depending upon the data required for each tip, e.g. here’s the output from another selection:
The reconfiguration is controlled by a ReportType parameter with values “01”, … ,”08″, that’s used as an input to expressions that control grouping, visibility & sorting.
Two of the report types have grouping but the rest don’t. So a single row group exists with the following group expression that controls whether & how to group:
=IIF(Parameters!ReportType.Value = “04”, Fields!ItemPath.Value,
IIF(Parameters!ReportType.Value = “05”, Fields!UserName.Value, Nothing))
Visibility for each column is handled by expressions of the form:
=IIF(InStr(“01,02,04,05,06”, Parameters!ReportType.Value) > 0, False, True)
Sorting is also expression based, albeit slightly more complicated. There are 3 levels of sort:
The first 2 are there to account for sort direction not being configurable via expressions, where the first level handles report types where a top level ascending sort is required:
=Switch(Parameters!ReportType.Value = “01”, 0,
Parameters!ReportType.Value = “02”, Fields!Source.Value,
Parameters!ReportType.Value = “03”, Fields!ItemPath.Value,
Parameters!ReportType.Value = “04”, Fields!Source.Value,
Parameters!ReportType.Value = “05”, 0,
Parameters!ReportType.Value = “06”, 0,
Parameters!ReportType.Value = “07”, 0,
Parameters!ReportType.Value = “08”, Fields!Status.Value)
and the second level handles report types where a top level descending sort is required:
=Switch(Parameters!ReportType.Value = “01”, Fields!DurationMins.Value,
Parameters!ReportType.Value = “02”, 0,
Parameters!ReportType.Value = “03”, 0,
Parameters!ReportType.Value = “04”, 0,
Parameters!ReportType.Value = “05”, Fields!DurationMins.Value,
Parameters!ReportType.Value = “06”, Fields!MemoryKBProcessing.Value,
Parameters!ReportType.Value = “07”, Fields!ByteCount.Value,
Parameters!ReportType.Value = “08”, 0)
The third sort level exists for a couple of reports that require a secondary sort (ascending only).
So that’s about it for the report setup. The approach does provide for a very clean report design:
It uses one dataset, that reads from the ExecutionLog3 view in the Report Server database. The ScalabilityTime and EstimatedMemoryUsageKB data come from the XML structure in the AdditionalInfo column, so I used XQuery methods in the SELECT statement to extract these, e.g.:
SELECT AdditionalInfo.value(‘(AdditionalInfo/EstimatedMemoryUsageKB/Processing)’, ‘int’) AS MemoryKBProcessing, …
The report is very easy to adjust if different columns or report types are required. Candidate data for being added into the report are the performance-related elements that are new in the ExecutionLog3 AdditionalInfo XML structure, i.e. the ExternalImages and Connections elements. The former helps identify whether the retrieval of external images from web servers is slowing report performance, and the latter includes statistics regarding data retrieval from individual datasets.