Prevent SQL Server Reporting Services Slow Startup


By:   |   Updated: 2012-09-07   |   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

‹Service›
   ...
   ...
   ‹RecycleTime›720‹/RecycleTime›
   ...
   ...

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.

‹Service›
   ...
   ...
   ‹RecycleTime›1440‹/RecycleTime›
   ...
   ...

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".

Windows Task Manager with Reporting Services Memory Usage Highlighted

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\.

‹Service›
   ...
   ...
   ‹WorkingSetMinimum›250000‹/WorkingSetMinimum›
   ...
   ...

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


Last Updated: 2012-09-07


get scripts

next tip button



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

View all my tips




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name
*Email
Email me updates

Signup for our newsletter

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.





Monday, November 09, 2015 - 1:20:55 AM - Jenny Back To Top

Is there any option to pause SSRS report (only selected reports) for sometime.

We have scheduled maintenance work on database weekly, during maintenance we do not want our clients to view the reports as there may be some incorrect data in the reports. Any possibility to pop a warning message on report manager when they access reports during maintenance work?

 

 


Friday, October 30, 2015 - 10:06:50 AM - Sarma Pisapati Back To Top

Thanks for the tip. I have a fundamental question. If a client program is accessing SSRS during the time when the recycle is set, does SSRS wait until the request is completed before it recycles? For example, if we set restart @ 11:00 PM ET, and a client program is running at the time, does SSRS wait until the client program is completed?

Thanks in advance,

SP

 


Tuesday, January 27, 2015 - 1:21:16 PM - José Esteban Cruz Torres Back To Top

In SSRS 2014 not working with ‹WorkingSetMinimum›250000‹/WorkingSetMinimum>


Monday, August 25, 2014 - 4:28:16 PM - Amit Back To Top

Excellent Thank you!!!


Monday, January 27, 2014 - 5:29:23 PM - OD Back To Top

I have this 720 mins setting in the configure file but reporting serviice didn't restart in this cycle.  I am looking at the event log of windows and see no restart of reporting service.  Am I missing something here? 


Saturday, September 07, 2013 - 6:33:23 AM - kiquenet Back To Top

any ps1 script sample for modify values in config like memory and recycle time ?


Thursday, July 11, 2013 - 5:27:05 AM - David bridge Back To Top

Issues in the forum made the last comment not work correctly.

 

The issue is with the angle brackets. These were removed from my comment.

 

Dave


Thursday, July 11, 2013 - 5:25:03 AM - David bridge Back To Top

Hi Norman,

 

This is an excellent article but there is a slight bug in the way this web page is rendered which, if someone were to cut and paste the code,  can break their reporting server instance.

 

If you cut and paste the examples in to your config file using Visual Studio 2010 you will see the code entered correctly but in truth it isn't. This is probably a page encoding error.

 

Your example

 ‹WorkingSetMinimum›250000‹/WorkingSetMinimum›

 

contains the characters "

 

It looks ok in the editor but it will stop the config file from loading due to an invalid character.

 

If you use notepad to put the code in to the config file you will see the characters as a pipe "|" so it is more obvious that you need to correct the issue.

 

<WorkingSetMinimum>250000WorkingSetMinimum>

 

David Bridge

David Bridge Technology Limited

 


Saturday, June 22, 2013 - 2:00:31 PM - Vinay Back To Top

Thanks for your Post!!!

Does these two issues exists SSSRS 2012 ??


Tuesday, September 11, 2012 - 7:22:53 PM - Norman Chan Back To Top

Hi AGL,

Yes that's correct you'll need to restart reporting services for new "Recycle Time" to take affect.

Thanks,
Norm

 

 


Tuesday, September 11, 2012 - 4:48:47 AM - AGL Back To Top

Hi Norm,

if I change "RecycleTime" from 720 to 1440, should I restart SQL Server Reporting Service to changes take effect or it's not necessary ?

Thanks.


Saturday, September 08, 2012 - 7:12:19 PM - Norman Chan Back To Top

 

Hi Danie,

ref: http://technet.microsoft.com/en-us/library/cc263022(v=office.12).aspx

I'm not well versed with Viso or Excel Service, but possibly a similar method I've discussed with this tip could be used? Do you have access to your App Excel/Visio Services Settings? 

There are options for Memory Utilization (#6 referring to the link) 

You can work with settings for Max Private Bytes (be sure there's sufficient RAM and testing just in case), Memory Cache Threshold, and Max unused object age. Appears maybe extending unused object age might keep your excel/visio objects longer, which might help with slow startups. You'll likely need to restart services as well as part of this.

I'd be interested in learning about your results.

Norm

 

 

    

 

 

 

 


Friday, September 07, 2012 - 8:04:20 AM - Danie Swart Back To Top

What I am doing is running a Powershell Script to run a report. Do you know if there is something simular for Excel and Visio Services. \

My Powershell acess Viso and Excel Services as well but it appears to also have a slow start and then improves after the first access.

 

 



download

























get free sql tips

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



Learn more about SQL Server tools