SQL SSRS Cache Test

A report server can cache a copy of a processed report and return that copy when a user opens the report.

Cached reports can be stored in the disk or in CPU memory (in-memory cache). When in-memory cache is used, the report server does not query SQL Server for cached content.

Caching reduces direct report server accesses and related processing overheads. Caching can also shorten the time required to retrieve a report if the report is large or accessed frequently.

Poor cache usage therefore, often translates into poor report server performance. This is why, administrators should track cache usage, quickly detect sub-optimal usage of the cache, and investigate the reasons for the same. This can be achieved using the SQL SSRS Cache test.

This test monitors cache usage and reports the count of cache hits and misses, thus revealing whether more reports were serviced by the cache or by the report server itself. This way, the test sheds light on ineffective cache usage. The test also monitors the usage of in-memory cache and reports irregularities (if any). These timely revelations prompt administrators to quickly investigate and determine the reasons for poor cache usage - for instance, you can figure out if frequent cache flushes is the reason for cache misses. By rapidly fixing the bottlenecks to poor cache usage, administrators can improve the speed and efficiency with which report requests are serviced.

Target of the test : A Microsoft SQL Server Reporting Services server

Agent deploying the test : A remote agent

Outputs of the test : One set of results for the report server being monitored

Configurable parameters for the test
Parameters Description

Test period

How often should the test be executed

Host

The host for which the test is to be configured.

Port

The port at which the specified Host listens.

Instance

The name of a specific SQL Report server instance to be monitored. The default value of this parameter is “default”. However, if the Microsoft SQL Server hosting the SQL Reporting Server database uses named instances, then do the following:

  • Configure the Instance parameter with the name of the SQL Server instance that hosts the SQL Report Server database.
  • Do not change the default value of the Port parameter

Is Passive

If the value chosen is Yes, then the Microsoft SQL server (hosting the SQL report server database) under consideration is a passive server in a SQL cluster. No alerts will be generated if the server is not running. Measures will be reported as “Not applicable’ by the agent if the server is not up.

Measurements made by the test
Measurement Description Measurement Unit Interpretation

Cache hits

Indicates the number of report requests that were serviced by the cache during the last measurement period.

Number

Ideally, the value of this measure should be high.

Cache hit rate

Indicates the rate at which report requests were serviced by the cache.

Hits/Sec

 

Cache misses

Indicates the number of report requests that were not serviced by the cache.

Number

Ideally, the value of this measure should be 0 or very low. A high value for this measure indicates that many report requests have been serviced by directly accessing the report server.

This can happen if the reports requested are not available in the cache. If a majority of report requests have been fulfilled by directly retrieving the requested reports from the report server, it can delay request processing and degrade the overall performance of the server.

Cache misss rate

Indicates the rate of requests that were not serviced by the cache.

Misses/Sec

 

Cache flushes

Indicates the number of times the data in the cache was flushed to the disk.

Number

When the amount of unwritten data in the cache reaches a certain level, the report server writes cached data to a drive. This write process is called flushing.

A high value for this measure implies that the cache is flushed frequently. Frequent flushing can reduce the number of reports in cache, which in turn can increase cache misses. To avoid this, you may want to limit cache flushes.

Cache flush rate

Indicates the rate of cache flushes.

Flushes/Sec

In-memory cache hits

Indicates the number cached reports returned from the in-memory cache during the last measure period.

Number

A high value is desired for this measure.

In-memory cache hit rate

Indicates the rate of cached reports returned from the in-memory cache.

Hits/Sec

 

In-memory cache misses

Indicates the number of cache misses against the in-memory cache during the last measure period.

Number

A low value is desired for this measure.

In-memory cache miss rate

Indicates the rate of cache misses against the in-memory cache.

Misses/Sec