How to Speed up SSRS on First Run

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.

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

There are two ways to disable the idle timeout:

• uncheck the checkbox in front of the setting 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

• Keep this setting in mind the next time for any Reporting Services installation.
• Check out these related resources on MSSQLTips:
  • Post Installation Tasks for SQL Server 2005 Reporting Services
  • Script to determine SQL Server Reporting Services parameters, path and default values
  • How to Optimize Report Parameter Dropdowns in SQL Server Reporting Services (SSRS) 2005
  • Logging Options for Reporting Services 2005

Tibor Nagy

I am a technical and business professional based in Budapest, Hungary. I am working in the financial industry on the operations side, but also have test management experience in the educational software domain. My experience includes Microsoft SQL Server 2000-2012, IBM DB2 and MySQL database administration, troubleshooting, migration and development. My focus points are performance tuning and security. I am passionate about sharing my knowledge with the greater audience.

I hold MSc degree in Engineering and Management. I am married and I have a beautiful daughter.

LinkedIn account:
http://www.linkedin.com/in/nagytibor

Twitter account:
https://twitter.com/#!/tnagy_eeft