Slow response on opening first report and after a certain timeout period or after SSRS restart

The first call to reporting services is very slow if you access report after some long interval or your reporting services have been restarted. This is as per design as when reporting services have been idle for a time, the next hit on it can be extremely slow and this is with every report not any individual and related to IIS. The problem is that when the IIS is not getting any traffic, it “goes to sleep”, to clear up memory for other applications. This is totally understandable and a side-effect of Reporting Services’ architecture. Consider that RS is a managed code application that’s persisted on disk as an executable but in intermediate language which must be compiled to machine-code before it can be run. This compile phase is only repeated when the service has not been used in a while. If left idle, the system will mark the assembly as “discarded” and permit it to be overlaid by other operations. 

There are some solutions to it, depending of your version of IIS and SQL server.

Reporting services 2005 with IIS 6 and above

  • Open IIS manager
  • Under application pools right click DefaultAppPool and select properties.

AppPoolNew

  • Under the performance tag, you can change the value for the idle timeout. Default is 20 minutes.

AppPool1

There are two ways to disable the idle timeout:

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

 AppPool_fixed

 After this change I found that earlier first report took 2-4 minutes to generate, but now it is ready in a few seconds.

Reporting Services 2008\2008R2
In SSRS 2008 and above version there is 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.
This setting can be changed in report server configuration file rsreportserver.config. This file is located in your SSRS installation directory, for example D:\Program Files\Microsoft SQL Server\MSRS10.MSSQLSERVER\Reporting Services\ReportServer
As the suitable time for SSRS recycle depends on the usage and you can adjust It as per your need in the configuration file as shown below
‹RecycleTime›1440‹/RecycleTime›

Another Workaround
There is one more possible workaround solution to this issue which rests on the scheduler and execution of a PowerShell script, which stops and starts the SSRS service and after the restart it makes a request to the report manager URL which forces the reporting services to load all the configurations. Then all the subsequent request to SSRS are immediate.
Hence every morning you can schedule below script to restart SSRS and load all configuration before the business operations starts.

Stop-Service “SQL Server Reporting Services (MSSQLSERVER)”
Start-Service “SQL Server Reporting Services (MSSQLSERVER)”
$wc = New-Object system.net.webClient
$cred = [System.Net.CredentialCache]::DefaultNetworkCredentials
$wc.Credentials = $cred
$src = $wc.DownloadString(“http://localhost_SQL2008_Prod/Reports/Pages/Folder.aspx“)

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s