While doing research for an upcoming add on to our standard Health Checks, I have been reviewing the different option to set in the configuration file for SQL Server Reporting Services. There are memory settings which should be addressed, especially if you are running Reporting Service on the same server as your Database Engine.
Reporting Service; just like the Database Engine can use all available memory if there is no limitation set. There are 2 values which SQL Server calculates for you if not set in the configuration file for Reporting Services. These settings are called: WorkingSetMaximum and WorkingSetMinimum.
WorkingSetMaximun is the maximum amount of memory Reporting Service can use before rejecting connections. By default, this is set to the same amount of memory of the server and is calculated at startup.
WorkingSetMinimun is the minimum amount of memory allocated to Reporting Service at startup. The default value is 60% of WorkingSetMaximun. Now one would think it will use this amount at startup, this is incorrect. If there is memory pressure on the server, and the used amount of resources by reporting service is less than this setting, the service will not release what resources it currently is using. But if Reporting Service is used more than this setting it will release the resources back to the OS until it has decrease the value of WorkingSetMinimun.
These 2 settings are not located in the RSReportServer.config. If you wish to modify the settings, you will need to add the following into the config file under the root.
<WorkingSetMaximum>2097152</WorkingSetMaximum><WorkingSetMinimum>1258291</WorkingSetMinimum>The above example set the max memory for reporting service to 2GB with a working min memory a little over 1 GB.
Assume you have a server with a total of 12GB of memory, and you have allocated SQL Server to have a max Memory of 8. This leaves a total of 4GB for the OS and other applications including Reporting service. If you do not change the WorkingSetMaximum for reporting service; at start up the WorkingSetMaximum will be set to 12GB with a WorkingSetMinimun of 7.2GB. There could be cases where SQL Server is using all 8GB assigned and Reporting Service is attempting to use the 7.2. There will be no memory left for the OS and performance will greatly depreciate.
So, if you have a server running SQL Server and Reporting Service, insure you are setting the WorkingSetMaximun to a value less than the memory left for the OS after SQL Server is configured.