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

By: Tibor Nagy   |   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 2005 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.

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.

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

Last Update: 1/3/2011

About the author

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

View all my tips

We Recommend

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 Signup for our newsletter

Comments *** NOTE *** - If you want to include code from SQL Server Management Studio (SSMS) in your post, please copy the code from SSMS and paste the code into a text editor like NotePad before copying the code below to remove the SSMS formatting.
*Enter Code

 

Sponsor Information

SQL Server having some performance issues? Idera SQL check. FREE SQL Server enhancement. NEW! Top 5 hard-earned lessons of a DBA from Grant Fritchey & the DBA Team. Read it now Need SQL Server help and not sure where to turn? Reach out to expert consultants in the USA for a Health Check. In two mouse clicks view SQL bottlenecks. With ZERO impact pinpoint all poor performing SQL with 100% accuracy. The SQL Server Security THREAT - It’s Closer Than You Think