SSRS Memory Pressure Zones

Imagine the following scenario: You are an unpopular high-school kid who is desperate to make friends. To try and realize that goal, every Friday night you throw a party and pass out invitations to anyone you see (with a question-mark for the party end time because that’s just how happening you are). From time to time one or two people might show up, but it is usually dead. Then, one Friday night, for whatever reason people actually showed up - a lot of people. You are excited and nervous and are so caught up in the moment that you let more and more people into the house. More people than can actually fit in your house comfortably. Your new party guests begin to fight over everything. They fight over punch (for the purposes of this high-school story, the punch has not been spiked) and standing room and they basically just begin to tear the place apart, bringing the party to a standstill.

While waiting for the police to arrive you begin to think to yourself – “Perhaps I shouldn’t have let all those people in at once. I should have been keeping an eye on my limited resources and adjusted appropriately. I'm smart enough to handle this, why did I fail?”

Welcome to SQL Server Reporting Services Memory Pressure Zones, or SSRSMPZ as I hope no one ever calls it.

The real-world scenario: We had a report which contained approximately 20 sub reports. This report was working fine in our production environment and would run successfully in our less-powerful staging environment.  However, I began to notice that while it was running in our staging environment, other reports which were executed during the report run would begin to fail with an error of “An error occurred within the report server database. This may be due to a connection failure, timeout or low disk condition within the database”  and yet none of the criteria seemed to fit.

Rather than walk you through my descent into insanity while trying to research everything I will instead bring you straight to the eventual resolution.

As in the party scenario above, our under-utilized staging server found itself going from next to nothing, to over capacity, without reacting to the situation at all, until it was too late and resource allocation issues arose.

The settings we are going to deal with are located in the rsreportserver.config file on your report server(s).

Memory Pressure Zones and the server response are defined as:

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.

Medium: 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.

These zones are defined by the following settings within the rsreportserver.config:

MemorySafetyMargin and MemoryThreshold are stated values of 80 and 90, respectively.
WorkingSetMinimum and WorkingSetMaximum are not stated by default and are instead determined at service start time.
WorkingSetMaximum = Amount of available memory when the service starts.
WorkingSetMinimum = 60% of WorkingSetMaximum.

In our case, we made the following change:

<MemorySafetyMargin>60</MemorySafetyMargin>
<MemoryThreshold>70</MemoryThreshold>

This resulted in our staging environment being more aware of its resources and forced it to begin micro-managing requests earlier in the process.

As stated on MSDN: “Changing the configuration settings is useful only if requests are getting dropped before they complete” which correlates to the errors we were seeing earlier.

With these changes in place, we found that our problem went away and everyone lived happily ever after. OK, well, at least the problem went away.

I hope someone might find this useful.

Before I go, I would like to thank Stacia Misner (BLOG | Twitter) for walking with me down some dead-end rabbit trails and for helping me discover the correct path.

Further information on Configuring Available Memory for SSRS may be found via MSDN.

Have a great weekend! (unless you are reading this on a non-Friday, in which case, Have a great weekend! …eventually)