SSRS Report Speed

Overview

SSRS Report Speed answers two questions about a SQL Server Reporting Services instance that are easy to confuse and have different answers:

  • Which report is slow?
  • Which report is costing this server anything?

A report that averages two minutes and runs fifty times a month is slow. A report that averages nine seconds and runs eleven thousand times is expensive. They need different attention, and the second one is usually the one making mornings unpleasant.

The page opens ranked by total server time, and every bar is split three ways: the time spent fetching the data, the time spent processing it, and the time spent rendering it. That split is the difference between a query to tune and a report definition to fix.

The SSRS Report Speed report: phase split bars ranked by total cost, above the grid
Each bar is a report’s average run, split into its three phases, with a tick where its worst single run landed.

What this replaces

The previous version of this page showed five grey boxes counting how many reports averaged under a second, one to ten seconds, ten to sixty seconds, one to ten minutes and over ten minutes, above a grid sorted by average duration. It read the execution log twice to produce them.

Everything new on this page comes from columns that query was already reading:

  • The three phase times were selected, added together, and the split thrown away.
  • Status was in the same rows, so a report failing a third of the time looked identical to one that never failed.
  • Source was in the same rows, so a report served entirely from cache looked as expensive as one hitting the database every time.

The five duration bands survive as the Bands view, because clicking one to filter the grid was the one genuinely good idea on the old page.

Where to find it

This report is only shown for a database that holds a Reporting Services catalog. It is not available for an ordinary user database, and that is deliberate.

  • Tree: expand the report server database, then Real Time > SSRS > Report Speed.
  • Overview: click any band on the Reports by average duration panel on the report server database’s overview page, which opens this report filtered to that band.

Requirements

  • A Reporting Services catalog database, normally called ReportServer. The report is offered when dbo.ExecutionLog and dbo.Catalog both exist in the database being looked at.
  • SELECT on dbo.ExecutionLog, dbo.Catalog and dbo.ConfigurationInfo.
  • SSRS 2008 R2 and newer additionally expose dbo.ExecutionLog3, which this report uses when it is there. It falls back to dbo.ExecutionLog on older versions and loses nothing this page draws.

The three views

Ranked

The SSRS report speed bars
Each bar is one report’s average run, split into data, processing and rendering.

One row per report. The bar is the average run split into its three phases, darkest first:

Segment What it is What a large one means
Data retrieval Running the report’s datasets against their data sources The query is the problem
Processing Grouping, sorting, aggregating and filtering inside the report engine The report is doing work the query should
Rendering Turning the processed data into PDF, Excel, HTML or whatever was asked for The report definition is the problem

The thin vertical tick to the right of a bar is that report’s worst single run. The dotted line between the average and the tick is how far apart the two are. A report whose worst run is four times its average is contended rather than uniformly slow, which is a different fix.

Rows are ranked by total server time rather than by average, so the report actually hurting the server sorts to the top.

Cost

Times run on the horizontal axis against average duration on the vertical, both logarithmic, with each bubble’s area carrying the total server time that report has consumed.

The tinted corner at the top right is reports that are both slow and frequent. That is the only combination worth an afternoon, and it is the corner the old page’s ranking sorted away from.

Both axes are logarithmic because a report server’s range genuinely is that wide: six runs and eleven thousand runs belong on the same page, as do half a second and four minutes.

Bands

The five duration bands, drawn proportionally so a band holding one report no longer takes as much of the screen as a band holding twenty six. Click a band to filter the grid to it; click it again, or press Show all reports on the toolbar, to clear the filter.

Reading the grid

The SSRS report speed grid
One row per report, with the phase split behind the totals.
Column What it holds
Report The catalog item name
Path Its folder path in the report server
Where the time goes The three phase split, scaled against the slowest report on the page
Average Mean duration of one run
Worst run The longest single execution recorded
Runs How many times it ran inside the retention window
Total time Runs multiplied by average – the number the page ranks on
From cache Share of runs served from cache, a snapshot or report history rather than live data
Not succeeded Executions whose status was not rsSuccess
Last run When it last ran

Sorting works on the underlying numbers rather than on the formatted text, so 1.9s sorts above 900ms rather than below it.

What the totals actually cover

Every total, count and average on this page is bounded by how long the report server keeps its execution log. That is the ExecutionLogDaysKept setting in dbo.ConfigurationInfo, and it defaults to 60 days.

The page reads that setting and says so in the line under the headline. It matters because two report servers with different retentions are not comparable, and because reconfiguring Reporting Services empties the log – which makes a busy server look brand new.

Findings

The page derives a short list of sentences about itself and counts them in the line under the headline. They come from numbers already on the chart:

  • A single report that is a quarter or more of all report time on the server.
  • Reports that spend more time rendering than fetching, which points at the report rather than the query.
  • Reports whose worst run is at least four times their average, which reads as contention.
  • Executions that did not succeed.

Actions

  • Click a bar or a bubble to select that report’s row in the grid.
  • Double click to open SSRS Reports Run, which holds the individual executions these averages are of.
  • Right click a report for its recent runs, the users page, or to copy its path.
  • Right click anywhere else on the chart to copy the whole chart to the clipboard.

Where the data comes from

dbo.ExecutionLog3 when the report server has it, otherwise dbo.ExecutionLog, joined to dbo.Catalog and grouped to the catalog item.

Two details worth knowing:

  • The three phase times are millisecond integers, and both AVG and SUM over an integer expression accumulate in an integer, which overflows once a busy report has run for a cumulative twenty four days. The first term is cast to BIGINT so the whole expression is 64 bit.
  • Rows are grouped by report name and path rather than by report id and instance name. A scale out deployment logs the same report once per node, and grouping by instance draws each node as a report of its own.

On SSRS 2008 R2 and newer, sort and bookmark navigation round trips are excluded, because they share an execution with the render that produced the report and counting them inflates the run count of any report somebody scrolls.

Frequently asked questions

Why is a report at the top of the list when its average is not the highest? Because the page ranks by total server time. Switch to the Cost view to see how often against how long, plotted against each other.

A report shows a large cache share. Is that good? Usually. It means the report is being served from cache, a snapshot or report history rather than running its datasets, so its run count is not costing the database what it appears to. It also means the data those readers are seeing is as old as the cache policy allows.

Why does the total not match what I get from the report server’s own logs? The page counts only catalog items of type 2, which is reports. Folders, resources, linked reports and data sources are excluded. It is also bounded by the execution log retention.