SQL SSRS Memory Status Test

Reporting Services responds to system resource constraints by adjusting the amount of memory that is allocated to specific applications and types of processing requests. Applications that run in the Report Server service and that are subject to memory management include:

  • The web portal, a Web front-end application for the report server.
  • Report Server Web service, used for interactive report processing and on-demand requests.
  • A background processing application, used for scheduled report processing, subscription delivery, and database maintenance.

Memory management policies apply to the Report Server service as a whole, and not to individual applications that run within the process.

If there is no memory pressure on the system, each server application requests some memory at startup, in advance of receiving requests, to deliver optimum performance when requests are eventually received. As memory pressure builds - i.e., as memory availability gradually navigates from a no pressure state to a low, medium, or high pressure state - the report server adjusts its process model, so requests can be prioritized, memory usage can be shrunk, and the pressure can be eased.

Sometimes, if the memory pressure is very high, the report server even resorts to dropping processing requests before they complete. Administrators can fine-tune configuration settings on the report server to make sure this does not happen. However, these settings need to be fine-tuned often, so they are in line with the dynamic memory demands of the report server. For this, administrators need to continuously track variations to the memory pressure state of the report server, figure out how often the server experiences memory contentions, and understand how severe the pressures were. This is where the SQL SSRS Memory Status test helps!

This test reports the current memory pressure state of the report server. By tracking this measure over time, administrators can figure out if memory pressure remains steady at a particular state or if it oscillates between states. In addition, the test also reports how often in the last second the report server requested for memory usage to be shrunk and how much memory was shrunk in the process. These are good indicators of how often the report server experienced a memory crunch. Using these useful pointers, administrators can easily figure out which configuration settings on the report server need to be tweaked so that requests are not dropped.

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

Memory pressure

Indicates the current memory pressure state of the report server.

 

The values that this test reports and their corresponding numeric values are discussed in the table below:

Measure value Numeric Value
No pressure 1
Low 2
Medium 3
High 4

To know how the report server processes requests at each of these memory states, refer to the table below:

Measure value

Description

No pressure

If there is no memory pressure on the system, each server application requests some memory at startup, in advance of receiving requests, to deliver optimum performance when requests are eventually received.1

Low

Current requests continue to process. New requests are almost always accepted. Requests that are directed to the background processing application are given a lower priority than requests directed to the Report Server Web service.

High

Current requests continue to process. New requests might be accepted. Requests that are directed to the background processing application are given a lower priority than requests directed to the Report Server Web service. Memory allocations for all three server applications are reduced, with relatively larger reductions to background processing to make more memory available for Web service requests.

High

Memory allocation is further reduced. Server applications that request more memory are denied. Current requests are slowed down and take longer to complete. New requests are not accepted. The report server swaps in-memory data files to disk.

If memory constraints become severe and there is no memory available to handle new requests, the report server will return an HTTP 503 server unavailable error while current requests are completing. In some cases, the application domains might be recycled to immediately reduce memory pressure.

Note:

By default, this measure reports the Measure Values listed in the table above to indicate the current status of this index. The graph of this measure however, represents the same using the numeric equivalents.

Memory shrink amount

Indicates the size of memory in use that the report server requests to shrink.

MB

 

Memory shrink notification

Indicates the number of notifications that the server issued in the last second to shrink the memory in use.

Number

A high value for this measure indicates that the memory pressure on the Report server is in high.

By tracking the value of this measure over time, you can figure out how often the server experienced memory pressure.

If the frequency of memory contentions increase, it could cause processing requests to be frequently dropped by the server without completing. To avoid this, you may want to fine-tune one/more of the following configurations:

  • WorkingSetMaximum: Specifies a memory threshold after which no new memory allocations requests are granted to report server applications. By default, the report server sets WorkingSetMaximum to the amount of available memory on the computer. This value is detected when the service starts. When the value for WorkingSetMaximum is reached, the report server does not accept new requests. Requests that are currently in progress are allowed to complete. New requests are accepted only when memory use falls below the value specified through WorkingSetMaximum.If existing requests continue to consume additional memory after the WorkingSetMaximum value has been reached, all report server application domains will be recycled.
  • WorkingSetMinimum: Specifies a lower limit for resource consumption; the report server will not release memory if overall memory use is below this limit. By default, the value is calculated at service startup. The calculation is that the initial memory allocation request is for 60 percent of WorkingSetMaximum.
  • MemoryThreshold: Specifies a percentage of WorkingSetMaximum that defines the boundary between high and medium pressure scenarios. If report server memory use reaches this value, the report server slows down request processing and changes the amount of memory allocated to different server applications. The default value is 90. This value should be greater than the value set for MemorySafetyMargin.
  • MemorySafetyMargin: Specifies a percentage of WorkingSetMaximum that defines the boundary between medium and low pressure scenarios. This value is the percentage of available memory that is reserved for the system and cannot be used for report server operations. The default value is 80.

Although you can change the configuration settings, doing so will not improve report processing performance. The best way to improve server performance is to deploy the report server or individual report server applications on dedicated computers.