The same report runs every morning for the same group of readers. Report caching can spare SQL Server repeated work, while snapshots give readers a fixed point in time.
Separate Live Runs, Report Caching, and Snapshots
A live report runs its datasets when a reader requests it. A cached instance keeps a temporary processed copy for reuse under configured conditions. A snapshot captures a report at a scheduled point in time and presents that fixed result. These options serve different freshness and reproducibility needs.
I ask how current the numbers must be. A compliance report needing a consistent month end view benefits from a snapshot. A frequently opened operational report can use caching if a short delay is acceptable. A live report still makes sense when each request needs the latest committed rows.
Caching does not remove all database work. Different parameter combinations can create different cached instances, and cache expiry causes fresh executions. Model the combinations users actually choose before promising a dramatic reduction in source queries.
Choose a Freshness Rule Readers Understand
Set a cache expiration time or schedule that matches the business process. If the source loads every night, a cache refresh after that load makes sense. If data changes every few minutes, an hours old cache can confuse readers even while it improves performance.
Show the report’s data time or snapshot time. A reader should know whether a number reflects the last load, the last live run, or a fixed snapshot. The absence of a freshness label can turn a technically correct cached result into a support ticket.
I prefer to state the rule in plain words: “This report refreshes after the nightly load.” That sentence is easier to verify than “cache enabled.” The report owner can then check whether the schedule still matches the data process.
SELECT name, enabled
FROM msdb.dbo.sysjobs
WHERE name LIKE N'%Report%'
ORDER BY name;Account for Parameters in Report Caching
A cached report with parameters can maintain separate cached instances for different parameter values. A report with many combinations will reuse fewer results than one with a common default. A snapshot has a defined parameter context, so review which choices users need from it.
If the report uses user-specific data or security, test whether caching is allowed and how identity affects reuse. Do not assume two readers can safely share one processed result. SSRS execution settings and credentials affect what can be cached. Protecting data comes before reducing query count.
I look at the most common parameter sets. If nearly everyone opens the same region and date range, cache reuse can be useful. If every request has a unique customer ID, caching each result can cost more storage and administration than it saves.
SELECT TOP (50) ItemPath, Parameters, TimeStart,
TimeDataRetrieval, TimeProcessing, TimeRendering
FROM ReportServer.dbo.ExecutionLog3
ORDER BY TimeStart DESC;
Use Snapshots for a Fixed View
A snapshot stores a processed report at a point in time. It supports a consistent view even if the source changes later. Schedule it after the source load and validation complete, not simply at a convenient clock time. A snapshot taken before the load finishes preserves the wrong moment very reliably.
Keep snapshot history only as long as the business needs. More retained instances consume report server storage and require backup planning. Name the retention and access rule. A fixed record is useful only when people can find the correct period.
I test the snapshot’s source credentials and schedule under the actual service account. An interactive report that runs under my account can fail when the server executes it unattended. That difference tends to appear on the first unattended morning.
SELECT name, create_date, state_desc
FROM sys.databases
WHERE name IN (N'ReportServer', N'ReportServerTempDB');Preload and Invalidate Deliberately
A cache can be warmed by a scheduled execution or refresh plan so the first reader does not pay the query cost. Align that work with the data load. A warm cache built before publication still contains old data, even if the report opens quickly.
Changes to report definitions, parameters, data source credentials, and execution settings can invalidate cached content. Plan for a burst of fresh executions after deployment. A busy report released during peak usage can surprise the database even though caching is configured.
I check the first request after a report change. Does it hit the cache, create a new instance, or run live? Execution logs and SQL Server workload evidence answer that. A cache icon in a settings page is not proof of reuse.
Measure the Work Report Caching Saves
Compare data retrieval time, processing time, rendering time, and execution count before and after a cache change. A slow render can remain slow even when the source query is avoided. A source query can also remain expensive if every parameter combination misses the cache.
Look at database CPU and reads during common report periods. Query Store can show whether the report statements execute less frequently. Keep the same report usage pattern in mind while comparing. A quiet day can make any change appear successful.
I ask whether readers received the freshness they expected. Performance and correctness travel together here. If the cache makes a report fast but stale beyond its stated rule, the saved reads have bought the wrong result.
SELECT TOP (20) ItemPath, COUNT(*) AS executions,
SUM(TimeDataRetrieval) AS data_time_ms,
SUM(TimeRendering) AS render_time_ms
FROM ReportServer.dbo.ExecutionLog3
GROUP BY ItemPath
ORDER BY executions DESC;Make the Operating Choice Visible
Document why each busy report is live, cached, or snapshotted. Include refresh schedule, parameter behavior, credentials, and owner. Review the choice when the source load schedule or readership changes. A cache setting that was right last year can be wrong after a new morning process arrives.
Test failure behavior. If a scheduled snapshot fails, does the portal show an older instance, and can readers tell its age? If a cache expires while the source is unavailable, what message appears? Those questions matter more than a perfect happy path.
Caching and snapshots are practical ways to shift work away from the database. Use them with a named freshness rule and verify the actual execution pattern. Then the report remains both fast and honest about its data.
Document which schedule owns the cache refresh or snapshot creation. If the source load moves later, revisit that schedule. A report captured before the load finishes will be consistently fast and consistently stale.
Related reading on this blog: Data Sources and Data Sets in Reporting Services SSRS and What is SSRS and Why SSRS is asked for in many Job Opening?.
A cached report is not automatically a fresh report, it is a result governed by a freshness rule.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
