If you administer a SQL Server Reporting Services server, chances are you’ve had users complain the report manager is loading slow. You go out the report manager to investigate, and it loads normally. You have the end user try again, and this time it works fine for them too. You think about it for a while, and come to the conclusion the end user is crazy, or they had too many spreadsheets open on their PC. Turns out they aren’t crazy, and there is an explanation.
Recycling: It’s not just for humans
Reporting Services has a configurable setting in the RSReportServer.config file called ‘RecycleTime’. This value is defaulted to 720 minutes. If you leave this setting unchanged, every 12 hours the application domains for SSRS recycle. The first person to access the report manager after the recycle experiences the initial loading of the application domains, and thus the slow response time. The initial thought might be to just set the ‘RecyleTime’ setting to an extremely large value, and that will reduce the occurrence of the issue. This may work short term, but if the app domains aren’t recycled at a somewhat frequent interval, the greater the probability that a server wide issue will arise that affects everyone trying to access the report manager. Microsoft defaults to recycling the application domain so often to keep things happy and healthy, and while this makes for a good end user experience 99% of the time, what to do about that annoying complaint that comes in over your morning coffee? You’ve got to…
Take the power back
If you’ve ever manually restarted the reporting services service and then gone to make sure the report manager is working, you may have noticed the slow loading phenomenon occurs at this time too. When the reporting services service is restarted, it recycles the application domains as well. This also resets the ‘RecycleTime’ timer, and you’re good for another 12 hours. After fighting this issue for a while, we decided to schedule a job that restarts of the reporting services service outside of business hours, and then sends a web request to the report manager immediately after the service starts. For the web request portion, we used the Powershell solution detailed here: http://www.pawlowski.cz/2011/07/solving-issue-long-starting-report-ssrs-2008/. If you use this solution, remember to set the ‘RecycleTime’ value to something large so that it doesn’t trigger prior to your scheduled job running. We used our enterprise job scheduler to set up the job, but if you don’t have one of those, this could easily be accomplished using Windows task scheduler and the full script provided in the link. How often you schedule this depends on your environment, but once a week seems to do the trick for us.
The featured image for this post is courtesy of: http://epsos.de/Positive-Facts-about-Recycling-and-Waste-Management