WEBfactory 2010

SQL Server Reporting Services 2008 (SSRS 2008) Slow Startup

Abstract

Check out this article and learn how to overcome the Slow startup issue of the SQL Server Reporting Services 2008.

Description

SQL Server Reporting Services 2008 (SSRS 2008) starts slow when accessing the reports page. This slow startup is caused by a longer period of inactivity that can range from half an hour to a day.

Cause

The cause of the slow startup of SSRS 2008 can be one of the following:

  1. Unoptimized SSRS 2008 configuration: the RecycleTime is too short.

  2. Memory Usage: the Windows OS will take away some of the RAM allocated to SSRS 2008 after a period of inactivity.

Check the Solution section below to find out how to prevent the SSRS 2008 slow startup.

Solution

In order to make sure that SSRS 2008 will always have a normal startup, we will have to check and optimize both RecycleTime and Memory Usage.

Optimizing the Recycle Time

The RecycleTime is an SSRS 2008 setting found in the rsreportserver.config XML file located in the SQL Server Reporting Services 2008 installation directory (by default, at C:\Program Files (x86)\Microsoft SQL Server\MSRS10_50.WEBFACTORY2010\Reporting Services\ReportServer).

Capture2609.jpg

The RecycleTime setting allows the user to specify the time (in minutes) before the idle resources within SSRS 2008 are freed. After the period of time specified in the RecycleTime setting, the SSRS 2008 resources are freed and the re-initialization of SSRS 2008 resources can cause slow startups. By default, SSRS RecycleTime is set to 720 minutes (12 hours) but can be modified to suit the project's needs.

As the RecycleTime uses the last date and time at which SSRS 2008 was restarted as its reference point, SSRS will recycle its resources every 720 minutes (by default), running an endless recycle schedule in order to free resources.

Depending on the project, the RecycleTime value can be adjusted in order to trigger the recycling process less often and keep the resources for faster access. For example, the RecycleTime value can be set to 1440 minutes (24 hours), so that the slow startup can be avoided during a day. Multiple timezone projects can also benefit from a longer recycling time span, due to the inclusion of the timezone differences in the recycle time span.

Different projects will have different workflows, so the RecycleTime setting must be changed only after a serious analysis of the SSRS 2008 usage.

To modify the RecycleTime setting, follow the next steps:

  1. Open the rsreportserver.config XML file using the preferred text editor.

  2. Using the Find feature of the text editor (usually accessed by pressing Ctrl + F), locate the RecycleTime tag.

    Capture2610.jpg
  3. Modify the value between the opening tag and the closing tag. Set the time in minutes as desired (1440 minutes in our example).

    Capture2611.jpg
  4. Save the rsreportserver.config XML file.

Optimizing the Memory Usage

After an idle period, Windows will take some of the SSRS 2008 allocated RAM in order to help other memory-intensive software running on the same machine. This can be avoided in three ways:

  1. Adding more RAM to the machine. This is the straight-forward method but it is not always possible to modify the internal components of a production machine.

  2. Keeping SSRS 2008 active, so Windows won't take away any resources. This can be done by scheduling periodic reports (containing minimal data) in order to prevent the SSRS 2008 from going idle. For example: a simple report displaying current date (using GetDate()) or any simple query, scheduled every hour.

  3. Configuring the SSRS 2008 not to release memory back to the OS. While there is no default option for this in SSRS 2008, this setting can be manually added in the rsreportserver.config XML file. The amount of memory to be kept by SSRS 2008 should be carefully decided upon, so no other running software will go out of memory. The safest way is to study the memory usage of the ReportingServicesService.exe process while active and find an amount of memory that will suffice. The amount of RAM used by SSRS 2008 varies from one project to another.

Follow the next steps to force SSRS 2008 to keep the desired amount of RAM:

  1. Open the rsreportserver.config XML file (located by default at C:\Program Files (x86)\Microsoft SQL Server\MSRS10_50.WEBFACTORY2010\Reporting Services\ReportServer) using the preferred text editor.

  2. Locate the Service opening and closing tags <Service> and </Service> using the Find (Ctrl + F) functionality of the text editor.

  3. Add the new setting <WorkingSetMinimum>[InsertChosenValue]</WorkingSetMinimum> between the opening <Service> tag and the closing </Service> tag.

  4. Replace the [InsertChosenValue] placeholder value with the amount of memory, in kilobytes.

  5. Save the rsreportserver.config XML file.

For example, we choose to force SSRS 2008 to keep 250000 kilobytes from returning them to the OS when idle:

...
<Service>
	<IsSchedulingService>True</IsSchedulingService>
	<IsNotificationService>True</IsNotificationService>
	<IsEventService>True</IsEventService>
	<PollingInterval>10</PollingInterval>
	<WindowsServiceUseFileShareStorage>False</WindowsServiceUseFileShareStorage>
	<MemorySafetyMargin>80</MemorySafetyMargin>
	<MemoryThreshold>90</MemoryThreshold>
	<RecycleTime>1440</RecycleTime> <!-- the recycling period set in the previous step -->
	<MaxAppDomainUnloadTime>30</MaxAppDomainUnloadTime>
	<MaxQueueThreads>0</MaxQueueThreads>
	<UrlRoot>
	</UrlRoot>
	<UnattendedExecutionAccount>
		<UserName></UserName>
		<Password></Password>
		<Domain></Domain>
	</UnattendedExecutionAccount>
	<PolicyLevel>rssrvpolicy.config</PolicyLevel>
	<IsWebServiceEnabled>True</IsWebServiceEnabled>
	<IsReportManagerEnabled>True</IsReportManagerEnabled>
	<FileShareStorageLocation>
		<Path>
		</Path>
	</FileShareStorageLocation>
	‹WorkingSetMinimum›250000‹/WorkingSetMinimum›<!-- the amount of RAM to keep, expressed in kilobytes -->
</Service>
...