Slow response time for SQL Server Reporting Services after restart or after period of inactivity - why does it happen and how to fix it -
I can think of 2 separate issues and causes. First is related to the Web Service recycling and the time needed for restarting and reloading its components\assemblies. The second could be a question of response time for a report (the first time it is executed) and could be linked to the caching mechanism.
Below we look at the two different situations.
1. Problem caused by the loading of assemblies. NET after startup (restart) of SSRS Service.
Normally, a response time longer for the first service access SSRS after a restart of the service is normal. The behavior is observed even if the server and the service is not restarted but after a period of inactivity. This behavior can be linked to Recycle options for the IIS service.
One possible solution would be to check that the service is not restarted unnecessarily, and if it is still restarted, forcing the loading of components before users access it.
Full info on the topic of recycling:
https://msdn.microsoft.com/en-us/library/bb934330.aspx - Application Domains for Report Server Applications
The article describes the two parameters that we have to control the recycling of SSRS: RecycleTime and MaxAppDomainUnloadTime (file RSReportServer.config).
Following a restart of the service SSRS to ensure that the assemblies and necessary components are loaded, and the service will be available to users, we can put a up a solution for a query similar to a PING to the service.
For this task we have several options:
- A script for RS.EXE (https://msdn.microsoft.com/en-us/library/ms162839.aspx )
- Use a .NET application to access Reporting Services SOAP API.
https://msdn.microsoft.com/en-us/library/ms153968.aspx - Integrated Reporting Services using SOAP API
https://msdn.microsoft.com/en-us/library/ms155131.aspx - Using the SOAP API in a Windows application
- Use a simple application to access Reporting Services URL https://msdn.microsoft.com/en-us/library/ms154537.aspx
2. Problem of execution time for a particular report, the first time.
A different time (longer) for the first execution of a particular report is normal. executions that follow (if the report is run with the same parameters) will take the report cache, and therefore the time performances will be improved.
The document https://technet.microsoft.com/en-gb/library/cc966418.aspx - Planning for Scalability and Performance with Reporting Services contains a paragraph "Report Caching and Storage" on this subject.
We have the options:
- Loading the cache with the help of a subscription-driven data (Data-Driven Subscription) using the NULL Delivery extension.
Full Info:
https://msdn.microsoft.com/en-us/library/ms155927.aspx - Caching a report in Reporting Services
https://msdn.microsoft.com/en-us/library/ms155876.aspx - How to: preload the Cache (Report Manager)
- In the properties of the report, set the execution in order to use a cached copy. More info: https://msdn.microsoft.com/en-us/library/ms159241.aspx -- Set the properties of a given report
HTH,
Emi
---------------------------------
Also, check out Evan Basalik's post on SQLBlog about this: https://blogs.msdn.com/sqlblog/archive/2007/11/09/reporting-services-staaaarrrrrtttt-up.aspx