SSRS Caching and Execution

Overview

SSRS Caching and Execution puts two facts on one screen that a report server keeps in different places: how each report is set to execute, and how much work it has actually caused.

Every report is set to one of three things, and the setting is normally only visible by opening each report’s properties one at a time:

  • On demand. Every open goes to the data sources.
  • Cached. The first open runs it, and later opens get the stored answer until it expires, either after a number of minutes or on a schedule.
  • From a snapshot. The report only ever shows a rendering taken at a set time, and opening it never touches a data source.

The finding the page leads with is the crossing of the two: a report that is slow, runs often, and is not cached. The opposite finding is here too: a report cached for a day or more, whose readers may believe they are looking at current numbers.

Where to find it

Only shown for a database holding a Reporting Services catalog.

  • Tree: expand the report server database, then Real Time > SSRS > Caching and Execution.
  • SSRS Snapshots and History: the Caching toolbar button, or right click a rendering and choose What caches and what snapshots.
  • SSRS Schedules: the Caching toolbar button, or right click and choose What caches on a schedule.
The SSRS Caching and Execution report: reports ranked by total work above the grid
Reports ranked by the work they have caused, with the slow uncached ones in red.

Requirements

  • A Reporting Services catalog database. The SSRS pages are offered when dbo.ExecutionLog and dbo.Catalog both exist.
  • SELECT on dbo.Catalog, dbo.CachePolicy, dbo.ExecutionLogStorage and dbo.Subscriptions, plus dbo.ConfigurationInfo for the retention clause.
  • The query reads the ExecutionLogStorage table rather than one of the log views, so it works the same way on every version. It is given 180 seconds.

The two views

The toolbar has a two part switch, By cost and By staleness, and buttons that open SSRS Report Speed, SSRS Snapshots and History and SSRS Schedules.

By cost

The SSRS caching bars
One bar per report that ran inside the retention window, ranked by total seconds of work.

One bar per report that has at least one run in the execution log, ranked by total seconds of work rather than by average. A ninety second report run twice costs the server less than a nine second report run thousands of times, and ranking by average puts the wrong one at the top.

The label is the report name. The line under it is its mode, and its expiry when it has a cache policy.

By staleness

Only the reports that are cached or run from a snapshot, ranked by how many minutes a cached answer is allowed to live. The value on the right is the expiry in words. A report whose cache expires on a schedule, or that runs from a snapshot, has no minute count, so its row shows the words with no bar.

Colors and outlines

Color Meaning
Red Worth caching: not cached, run at least five times, and averaging ten seconds or more
Amber Cached for a day or more
Green Neither

Red and amber bars also get an amber outline. Hover over a bar for the path, mode, expiry, run count and average, how many runs never reached a data source, and the notes.

The chart draws as many rows as fit under the page’s chart height limit, and the note under it says when there are more. The grid holds every report.

Reading the grid

The SSRS caching grid
One row per runnable report, with its execution setting beside what it has cost.
Column What it holds
Report The catalog path
Mode On demand, Cached or From a snapshot
Notes What the page found about this report, in words
Expires For a cache: after so many minutes, hours or days, or on a schedule. For a snapshot: when it was taken, or when the schedule says. - for on demand
Runs Renders in the log for this report, not counting the sort, toggle, bookmark and find round trips inside one (see below)
Average Average seconds of work per run
Total work Total seconds of work across every run in the log
From cache Share of runs served from cache, a snapshot or history; blank when it never ran

Rows are in path order. Mode and Notes are drawn in red or amber on a flagged report. Notes takes the width the other columns leave; hover over a row for the full path and the full notes.

The Notes column can say:

  • It averages so long over so many runs and is not cached, so every one of those went to the data sources.
  • It is cached for a day or more, so a reader can be looking at numbers a day old without being told.
  • It only ever shows the stored rendering.
  • Caching is on and no run inside the window was served from the cache, which usually means it expires before the next reader arrives.
  • Its subscriptions send the snapshot rather than fresh numbers.

Findings

The headline counts the reports, how many are cached, how many run from snapshots, and how many are worth caching (or says nothing slow is uncached). The line under it adds, where they apply:

  • The largest report worth caching, with its total work and run count.
  • The most caching those reports could have saved in this window, when that is over a minute. This is an upper bound: it assumes every run after the first would have been served from cache, and a cache that expires between readers saves nothing.
  • How many reports are cached for a day or more.
  • What “worth caching” means: slower than ten seconds on average, run at least five times, and not cached now.
  • That durations are the report server’s three phases added up rather than the wall clock.
  • On SSRS 2008 R2 and newer: that a run is a render, and the round trips a reader makes inside a report are not counted, the same way SSRS Report Speed counts them.
  • How many days the execution log keeps, which bounds every count on the page.
  • That the page reads the catalog tables directly, so it covers every item on the report server.

Actions

  • Click a bar to select that report’s row in the grid.
  • Double click a bar to select that report’s row and open SSRS Report Speed, the page this one is meant to be read beside. Report Speed opens on every report.
  • Right click a bar for the same menu as that report’s grid row.
  • Right click a grid row, below the usual Copy, Copy with Headers and Select All, for:
  • Right click an empty part of the chart to copy the chart to the clipboard.

Nothing on this page turns caching on or off. The setting lives in two places that have to agree, and the web portal changes them together; this page is for finding which report to open there.

Where the data comes from

dbo.Catalog for catalog item types 2, 4, 12, 13 and 14, the runnable items, with:

  • ExecutionFlag for the execution setting,
  • a LEFT JOIN to dbo.CachePolicy for the cache expiry,
  • the run count, cache served runs, total milliseconds and last run from dbo.ExecutionLogStorage, grouped by report, and
  • a count of dbo.Subscriptions on the report.

Two details worth knowing:

  • The duration is TimeDataRetrieval + TimeProcessing + TimeRendering, each converted to BIGINT before the sum, rather than end time minus start time. The wall clock also contains the time a large rendering spent being sent to a slow client, which caching would not fix.
  • A run counts as served from cache when its Source is 2, 3 or 4: cache, snapshot or history.
  • On SSRS 2008 R2 and newer (when the catalog has the ExecutionLog3 view), a paginated or linked report’s runs are only the rows whose ReportAction is 1, Render. Sorting, toggling, jumping to a bookmark or a document map entry and finding text each write a row of their own under the same execution, and SSRS Report Speed leaves them out the same way, so the two pages agree on run counts and averages. Mobile reports, Power BI reports and Excel workbooks never log a Render action, so their rows are all counted.

Frequently asked questions

Why is a report that takes two minutes not flagged? It has to have run at least five times inside the execution log’s retention window as well as averaging ten seconds or more. A slow report that runs once a month is not what caching is for.

A report is cached, but From cache is 0%. Why? Every run in the window went to the data sources. The usual cause is a cache that expires before the next reader arrives, so each reader pays for a fresh run.

Can I turn caching on from here? No. Open the report’s properties in the web portal. The page deliberately writes nothing, because a cache setting written by hand into the catalog tables can leave the report server with a policy it does not honor.