Prevent SQL Server Reporting Services Slow Startup

By: Norman Chan   |   Comments (13)   |   Related: > Reporting Services Performance

Problem

SQL Server Reporting Services 2008 (SSRS 2008) starts up slowly after a period of inactivity when I try to access the reports page. Sometimes it's slow accessing it just half an hour later, sometimes hours later, or next day, but I can't figure out why or the pattern behind it? Check out this tip to learn more.

Solution

There are two key factors that impact Reporting Services slow start ups when it has been inactive for a period of time.

• SSRS Configuration (Recycle Time)
• Memory Pressure

It's important to understand factors mentioned above.  So let's dive in.

SSRS Configuration

SSRS has a XML configuration file with a setting for "Recycle Time". The recycle time is a scheduled timer in minutes based on when the last time SSRS has been manually restarted by a user. When a recycle occurs idle resources within SSRS are freed. This value can be changed to allow SSRS to keep resources for a longer period to prevent slowdowns due to re-initializing SSRS resources. SSRS is configured to recycle itself every 720 minutes, or 12 hours as shown below in the sample rsreportserver.config configuration file. This file is located in your SSRS installation directory, for example C:\Program Files\Microsoft SQL Server\MSRS10.MSSQLSERVER\Reporting Services\ReportServer

In the code above, the Recycle Time is every 720 minutes, but can be changed in rsreportserver.config. Since SSRS will recycle itself every 720 minutes, this initially leads to a slow start for the first user or process accessing it. Afterwards once users begin using the system there should not be slow responses as long as SSRS is actively used until the Recycle Time occurs again in 720 minutes.

The Recycle Time uses the last date and time SSRS was restarted as its reference point. SSRS will use that restart time as the current date and time to schedule Recycle Time from the configuration file. For example if Recycle Time is set for 720 minutes, and SSRS was restarted at midnight 00:00, then 720 minutes later at 12:00 noon, SSRS will recycle itself. Afterwards 720 minutes later at midnight 00:00 SSRS will be recycled again, and so forth. This recycling of SSRS continues indefinitely to free up resources.

Since the best time for SSRS Recycle time will depend on each company's usage. It might be best to do some analysis when SSRS is not being heavily utilized. By default it's recycled every 12 hours, but it could be better to keep SSRS active for longer period if there report requests are being processed.

For example we have a company with offices in New York, San Francisco, and Honolulu. The Report Server is located on the East coast. East coast time is 6 hours ahead of Hawaii-Aleutian Standard Time (HST). SSRS is heavily used in all office locations between the hours of 6am and 5pm daily. Since SSRS Server is located on the East coast, we can determine that at 5PM HST it will be 11PM Eastern Standard Time (EST). Based on the default configuration file, the Recycle time of 720 minutes will be too short a time window for the Honolulu office. A solution will be to change the Recycle Time to 17 hours between 6am EST to 5PM HST, but for simplicity we can use a 24 hour Recycle Time.

To plan the recycle time, we would need to do the following:

1. Schedule restarting SSRS at 11PM EST
2. Modify Recycle Time in the XML configuration file to 1440 minutes, which is equivalent to 24 hours.

As an SSRS administrator you can work with your customer base to determine a time that works best for your company.

SQL Server Reporting Services Memory Pressure

Depending on the size of your organization a stand-alone Report Server may be cost prohibitive. In those circumstances, some deployments of SSRS are on a shared machine with SQL Server and other applications/processes.  Since SSRS is running on a shared machine during the day, when RAM by applications/services are needed, Windows decides SSRS as one of its victims to take away RAM if it has been idle.  The simple solution to this issue is to add more RAM, but in most cases that might not be possible.

There are two strategies to address this issue.  First is scheduling periodic reports throughout the day and the second option is to adjust minimum RAM for SSRS to manage memory pressure.  Both options require some estimation and planning to be successful.

The first is a work around to maintain activity to prevent SSRS from becoming idle. Periodic reports generated throughout the day will keep SSRS active and can prevent Windows from taking away RAM. It's the reason during busy periods of the day SSRS does not take a long time to ramp to display the reports landing pages. If customers have daily reports generated, consider working with customers to scheduled reports throughout the day to help keep SSRS active. An alternative is to create a simple scheduled report to keep SSRS active running every hour. For example: A simple report to display current date using GetDate() or a simple query will work.

The second option is to consider adding an additional entry in your SSRS configuration file for "WorkingSetMinimum" measured in kilobytes. SSRS will not release memory back to the OS below this value set in the configuration file. By default the XML configuration is not added, and must be manually added to the file. This is useful when there is resource contention and we want Reporting Services to perform well. The best way to determine a conservative number is to monitor the Working Set Memory utilization of Reporting Services process (ReportingServicesService.exe) in task manager and use it as a baseline for the "WorkingSetMinimum".

In this example above, ReportingServicesService.exe is using about 246,608K after monitoring SSRS usage. We can conservatively use 250,000K as our minimum working set of RAM for this server. We can add an entry in the rsreportserver.config located in C:\Program Files\Microsoft SQL Server\MSRS10.MSSQLSERVER\Reporting Services\ReportServer\.

It's also important to caution you that forcing a minimum RAM working set can impact other applications/processes. This number should be determined after spending significant time monitoring Task Manager\Perfmon to observe SSRS RAM consumption levels with other applications/processes to ensure it does not adversely impact anything other processes. There can be negative side effects if we configure the Working Set Minimum too high, as SSRS will not be return unused RAM below what is configured to the operating system during inactivity. Applications and the Server itself may become slow or unresponsive. This is the reason we need to monitor current RAM usage to identify a baseline and test.

Conclusion

By understanding these factors for configuration and memory pressure on SSRS we as administrators can more effectively prevent slow start ups within SSRS. This keeps customers happier.

Next Steps

• More reading details on Recycle Time - http://technet.microsoft.com/en-us/library/bb934330.aspx
• Learn to create a report with report builder - http://www.mssqltips.com/sqlservertip/1690/introduction-to-sql-server-2008-report-builder-20
• More reading on configuring memory for Report Server Applications - http://technet.microsoft.com/en-us/library/ms159206.aspx
• Check out these additional resources:
  • SQL Server Reporting Services Tutorial
  • Reporting Services Administration tips
  • Reporting Services Development tips

About the author

Norman Chan is a SQL Server DBA with over 12 years of IT experience as a software developer and DBA.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips