First report and report after specific time interval starts a long time on MS SQL 2008 Reporting Services

Today an interesting article was published on MS SQL Tips: How to Speed up MS SQL Reporting Services on First Run.

It’s about solving a problem with slow loading of first report (or even accessing the report manager) in MS SQL 2005 Reporting Services. On SSRS 2005 the problem by default occurs every 20 minutes of idle time.

The same problem occurs also on MS SQL 2008 and MS SQL 2008R2 Reporting Services, but in 12 hours intervals. As Reporting Services 2008+ doesn’t use the IIS server and use it’s own integrated web service, solving the problem is quite different.

On SQL Server Reporting Services 2008 the problem occurs when an Application Domain is recycled and by default it’s 12 hours (720 minutes). After this time a new application domain is created an all new requests are redirected to the new application domain. Once the application domain is recycled, the new domain has to authenticate to the database server, load all necessary structures and this prolongs the first report startup after the domain is recycled.

If this default behavior (recycling every 12 hours) doesn’t meet your business needs as, you can modify the Report Server configuration file and specify a value which will be closer to your needs.

The configuration is stored in the rsreportserver.config file which is located in the installation directory of the Report Server instance. E.g. for default instance of SQL Server 2008 R2 Reporting Services it is located in ”C:Program FilesMicrosoft SQL ServerMSRS10_50.MSSQLSERVERReporting ServicesReportServer” directory.

The part which interests us is in the Service Node and it’s RecycleTime parameter:

  <Service>
    <IsSchedulingService>True</IsSchedulingService>
    <IsNotificationService>True</IsNotificationService>
    <IsEventService>True</IsEventService>
    <PollingInterval>10</PollingInterval>
    <WindowsServiceUseFileShareStorage>False</WindowsServiceUseFileShareStorage>
    <MemorySafetyMargin>80</MemorySafetyMargin>
    <MemoryThreshold>90</MemoryThreshold>
    <RecycleTime>720</RecycleTime>
    <MaxAppDomainUnloadTime>30</MaxAppDomainUnloadTime>
    <MaxQueueThreads>0</MaxQueueThreads>
    <UrlRoot>
    </UrlRoot>
    <UnattendedExecutionAccount>
      <UserName>
      </UserName>
      <Password>
      </Password>
      <Domain>
      </Domain>
    </UnattendedExecutionAccount>
    <PolicyLevel>rssrvpolicy.config</PolicyLevel>
    <IsWebServiceEnabled>True</IsWebServiceEnabled>
    <IsReportManagerEnabled>True</IsReportManagerEnabled>
    <FileShareStorageLocation>
      <Path>
      </Path>
    </FileShareStorageLocation>
  </Service>

By modifying the RecycleTime, you can shorten or prolong the recycling interval. The interval is specified in minutes and as mentioned above the default value is 720 minutes (12 hours).

Detail on the Application Domains for Report Server Applications can be found on MSDN. Details about the RSReportServer configuration file is also available on MSDN.

As mentioned in the documentations the MaxAppDomainUnloadTime is a time interval in minutes for which the Reporting Services are waiting for any current operation to finish during recycling the Application Domain. As It was mentioned during recycling Application Domain a new Domain is created and all new requests are directed to the new Application Domain and the current one is leave for the MaxAppDomainUnloadTime if there are currently some jobs running. If the jobs are not finished during the MaxAppDomainUnloadTime, the Application Domain is restarted and all jobs terminated.

If you have some some reports, especially scheduled one, which could take longer than the default 30 minutes to process, you can also consider to alter this parameter according to your needs. On the other side, if there is some job running for such a long time, it should be analyzed why it takes so long and whether it could be optimized in some way.

There is a lot of other interesting parameters in this configuration file, which could useful especially in the Scale-out SSRS Deployment scenarios. I will focus on some of them in one of the future posts.

For the overall workaround of this problem using PowerShell, see my later post Solving issue with first long starting report on SSRS 2008.

Advertisements

7 thoughts on “First report and report after specific time interval starts a long time on MS SQL 2008 Reporting Services

  1. Thanks Pavel, I knew about the RS 2005 slow-start reason due to IIS, but this has helped me enormously.

    I am wondering if there is a max time for the RecycleTime. Maybe setting it to 3 days would cover weekends. I remember in IIS 6 you weren’t able to set this higher than 1740 programmatically without running into a bug , (sorry can’t find a reference for that maybe there is a workaround).

    On the other hand, maybe its beneficial for RS2008/2008R2 to do this recycling occasionally. Perhaps, like SharePoint, RS has memory leaks or there is fragmentation, In that case perhaps I should leave the 12 hours, but set a dummy report to be generated at 7:30am everyday to ‘wake up’ the server.

    I’m still not sure of which way to proceed.

    • The real problem is to really chose a right value.

      As you suggested, you can setup the value to 12 hours, but it is necessary to take in mind, that the counter starts with the service start.

      So the in case of applying some patches and restarting the server during the night then the counter starts from that time.

      Probably the easiest way could be to set the value a little bit over one day and then out of business hours when the reporting services are not used schedule a job, which will restart the service (stop/start) and after the start run a dummy report as you have suggested (even report with an empty page) – probably help only accessing the web service.

      So after this you will have every day fresh and running reporting services without delays.

  2. Hi Pavel,

    Thank you very much for the great message. I have been concerned about the initial delay of SSRS Service for a while.

    I just want to add one thing: you need to run the script as administrator in order to stop and restart the Report Service, at least in my case, while the OS is Windows 2008 Server.

    I sent you a freind request from FB.

    Hua

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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s