Learn more about SQL Server tools



solving sql server problems for millions of dbas and developers since 2006 join MSSQLTips for free SQL Server tips
































Top SQL Server Tools



































   Got a SQL tip?
            We want to know!

How to Speed up MS SQL Server Reporting Services SSRS on First Run

MSSQLTips author Tibor Nagy By:   |   Read Comments (7)   |   Related Tips: > Reporting Services Performance
Problem

I set up a new instance of MS SQL Server Reporting Services, but I noticed that it starts up very slow and I have to wait for ages to access the site. I also noticed that it is always slow when it has not been used for a certain period of time.

Solution

The reason behind is that we have to wait for the application pool to spin up. During each start up, the SSRS web service reads and decrypts the rsreportserver.config file, it has to physically open up a socket connection between the two servers since the connection pool is empty, log into the database instance, etc. Also the web service has to make RPC calls into the Windows Service to get the encryption keys. There is an idle timeout value which forces the application to shut down after 20 minutes by default. We can tweak this timeout setting to have the application always up and running. There are two different methods and the first is applicable only to SSRS 2005.

Method 1 - SSRS 2005 only

First start Internet Information Services Manager from Control Panel -> Administrative Tools. Navigate to your server instance on the left side pane and expand it. You will find the Application Pools. Right click on it and choose the Properties option from the menu.

tweak the timeout setting in ssrs to have the application alway up and running

Choose the Performance tab in the Application Pool Properties window and you will see that the default setting Idle Timeout is 20 minutes.

application pool properties

There are two ways to disable the idle timeout:

  • uncheck the checkbox in front of the setting or
  • set it to 0.
uncheck the box or set it to 0

With the above described settings I managed to reduce the average response time of our SSRS instance by around 10%. The performance gain is more visible when we run the first report on a certain day. Earlier it took 3-4 minutes to generate, but now it is ready in a few seconds.

Method 2 - SSRS 2005 thru 2012

You can modify the "RecycleTime" parameter in RSReportServer configuration file for SQL Server 2005-2012. The RSReportServer.config file can be found at the following location depending on your MSSQL version:

  • SQLServer 2005: \Program Files\Microsoft SQL Server\\Reporting Services\ReportServer
  • SQLServer 2008: \Program Files\Microsoft SQL Server\MSRS10.\Reporting Services\ReportServer
  • SQLServer 2008 R2: \Program Files\Microsoft SQL Server\MSRS10_50.\Reporting Services\ReportServer
  • SQLServer 2012: \Program Files\Microsoft SQL Server\MSRS11.\Reporting Services\ReportServer
  • SharePoint mode: \Program Files\Common Files\Microsoft Shared\Web Server Extensions\15\WebServices\Reporting

The RecycleTime parameter value is in minutes, the default value is 720 (12 hours).

Next Steps


Last Update: 1/3/2011


About the author
MSSQLTips author Tibor Nagy
Tibor Nagy is a technical and business professional from the financial industry. His experience includes SQL Server 2000-2008, DB2 and MySQL.

View all my tips
Related Resources


print tip Print  
Become a paid author





join MSSQLTips for free SQL Server tips     



Comments and Feedback:
Monday, January 03, 2011 - 11:56:39 AM - Anil Das Read The Tip

So it's 2011 and this hint is for SQL Server 2005. Fair enough, but since your hint is not applicable to SQL Server 2008, you should put that in the title:

How to Speed up MS SQL 2005 Reporting Services on First Run


Wednesday, January 05, 2011 - 10:56:53 AM - Darcy Dupuis Read The Tip

For SQL Server 2008 you can make a modification to the "RecycleTime" parameter in your report server config file. It will be located in the following folder:

<drive:>\ Program Files\Microsoft SQL Server\MSRS10.MSSQLRS\Reporting Services\ReportServer\rsreportserver.config

I believe the value is in minutes.


Tuesday, January 25, 2011 - 9:37:13 AM - Tibor Nagy Read The Tip

Darcy,

Thank you for your valueable input. Indeed, the RecycleTime parameter can be used in SQL Server 2008 and the value is in minutes. The default is 720.


Tuesday, January 25, 2011 - 1:02:55 PM - Laura Cassidy Read The Tip

I found it useful.  My shop is still on quite a bit of old technology due to our manufacturing processes  Thanks.


Wednesday, January 26, 2011 - 1:42:43 AM - Thato Mantai Read The Tip

Can't you just run a dummy report first thing in the morning every morning? 


Wednesday, January 26, 2011 - 3:14:49 AM - Tibor Nagy Read The Tip

You can run a dummy report but what if you have to support a global system with users from all around the world? When is the morning?


Tuesday, December 18, 2012 - 6:25:18 AM - Alistair Read The Tip

@Thato - you would then need to run a dummy report every twenty minutes once it idles again.  That's a surprisingly bad suggestion.

In Windows Server 2008 you just need to right click on the application pool name and view application pool default settings. Thanks Tibor for this, much appreciated!



Post a Comment or Question

Keep it clean and stay on the subject or we may delete your comment.
Your email address is not published. Required fields are marked with an asterisk (*)

*Name   *Email Notify for updates



Comments
Get free SQL tips:

*Enter Code refresh code


 
Sponsor Information







Copyright (c) 2006-2014 Edgewood Solutions, LLC All rights reserved
privacy | disclaimer | copyright | advertise | about
authors | contribute | feedback | giveaways | free t-shirt | user groups | community | events | first timer?
Some names and products listed are the registered trademarks of their respective owners.