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

By:   |   Updated: 2011-01-03   |   Comments (7)   |   Related: > 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


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Tibor Nagy Tibor Nagy is a SQL Server professional in the financial industry with experience in SQL 2000-2012, DB2 and MySQL.

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

View all my tips


Article Last Updated: 2011-01-03

Comments For This Article




Tuesday, December 18, 2012 - 6:25:18 AM - Alistair Back To Top (21001)

@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!

Wednesday, January 26, 2011 - 3:14:49 AM - Tibor Nagy Back To Top (12710)

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?

Wednesday, January 26, 2011 - 1:42:43 AM - Thato Mantai Back To Top (12709)

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

Tuesday, January 25, 2011 - 1:02:55 PM - Laura Cassidy Back To Top (12704)

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

Tuesday, January 25, 2011 - 9:37:13 AM - Tibor Nagy Back To Top (12697)

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.

Wednesday, January 5, 2011 - 10:56:53 AM - Darcy Dupuis Back To Top (12497)

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.

Monday, January 3, 2011 - 11:56:39 AM - Anil Das Back To Top (12477)

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














get free sql tips
agree to terms