Solving issue with first long starting report on SSRS 2008

2011-07-312016-10-08 Pavel Pawlowski SQL Server, SSRSSSRS

As I wrote in my previous blog post First report and report after specific time interval starts a long time on MS SQL 2008 Reporting Services, a first report after specific time takes very long to start.

The issue is caused by the way how SSRS works and SSRS regularly restarts application domain after specific time period. After the application domain is restarted, then upon first request to the SSRS it needs to load all the settings and it takes quite a long time.

There is no real solving to the issue except increasing the interval between the application domain restarts from default 720 minutes to other value which meets your business needs more closer.

However even after increasing the value, then after the period is reached, the application domain is restarted and again the first request will take a long time. It could be ideal to optimize the interval so the app domain restart is done out of business hours. however even then fist report will take a long time.

Here is a possible workaround solution. It rests on the scheduler and execution of a PowerShell script, which stops and starts the SSRS service (which has the same effect as the application domain restart) and after the restart it makes a request to the report manager URL which forces the reporting services to load all the configurations etc. Then all the subsequent request to SSRS are immediate.

So if we set the RecycleTime in the rsreportserver.config to a value which is over one day let’s say 1500 minutes (it is 25 hours) and schedule the execution of the PowerShell script out of the business hours, each morning we will have SSRS ready without any delays. For details about modifying the RecycleTime take a look on my previous post mentioned above.

So here is the PowerShell script:

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/Reports/Pages/Folder.aspx

The script above first stops the SQL Server Reporting Service of the default (MSSQLSERVER) instance and immediately starts it again (stopping and starting the service has the same effect as application domain recycling). Then an webClient object is created which is used to fetch the Report Manager page which causes the reporting services to load all the settings. The page is read as string (it doesn’t matter how we read the page. Important is to make a request to initialize the reporting services) and it will take a longer time (like the first report start).

It is also important to get the DefaultNetworkCredentials of the user account under which the script will be executed. It is necessary to assign those credentials to the web client so it can authenticate to the reporting services.

Also it is important to mention that it is necessary to execute the script with elevated administrative privileges to be able to stop and start the service.

You can create a scheduled task using the Scheduled Tasks GUI or execute a below command to create the scheduled task from within a command prompt. The command prompt needs to be running with elevated administrative privileges.

schtasks /create /tn "SSRS Recycle" /ru UserName /rl highest /np /sc daily /sd 08/01/2011 /st 02:00 /tr "powershell.exe -noprofile -executionpolicy RemoteSigned -file c:scriptsSSRSRecycle.ps1"

This command creates a new scheduled task named “SSRS Recycle”, which will be run non interactively with elevated rights as UserName. The task will be executed daily at 02:00 am starting from 1st of August 2011 and will execute a PowerShell script SSRSRecycle.ps1 located in folder C:scripts.

For details about schtasks you can take a look on MSDN Schtasks.exe.

As mentioned in the beginning, it is not real solution to the problem with recycled application domains, however it provides an acceptable work around and you will have every day reporting services ready and available without any delays.

90 thoughts on “Solving issue with first long starting report on SSRS 2008”

ProfessorDavos says:

2011-08-09 at 00:24

Thanks Pavel, This is a very clever fix for this. Since my last question to you, I’ve set the recycle time to 3 days and started using some scheduled reports that run at 7am and I haven’t seen the problem again.

Reply
Dan Price says:

2011-10-10 at 00:49

Great work Pavel – you have the only elegant solution I was able to find that to this issue.

Reply
Chris says:

2011-11-21 at 10:26

Thanks a lot Pavel! This is working great and is the only real solution out there.

Reply
ITFC says:

2012-03-12 at 13:04
Hi Pavel, first off thanks for this article it really helped me out.

I had to go down a different route though for two reasons.

1) This method required giving permissions to start and stop a service to a user whose password didn’t expire.

2) The Recycle time is not the only reason SSRS recycles: see http://msdn.microsoft.com/en-us/library/bb934330.aspx

Therefore I took the following approach:

A) Find an event that is triggered when SSRS is recycled and make the scheduled task event driven. This eliminates the need to Stop and Start the service and caters for all other Recycle causes. Note, there is not clear event for this but I noted that the temp DB of SSRS has its compatibility level changed to 100 on recycle (this may be different per version but I’m sure the principle remains).

B) Create custom XML for the event trigger. See below. It may be worth trying your custom XML in the windows event viewer first to make sure it works.

C) Removed the stop/ start powershell from you script.

D) I called the Powershell script from a .cmd batch (see below) and out put powershell results to a file to help in debugging as some users/ environments UAC conditions you can’t get task scheduler to call powershell directly.

***CODE***
Put the following files into your scripts directory as mentioned in the code. I used C:DBASCRIPTSSSRSRecycle

***Powershell script “SSRSRecycle.ps1” copy code below ***
```
echo "starting pre-cache of SSRS site"
$wc = New-Object system.net.webClient
$cred = [System.Net.CredentialCache]::DefaultNetworkCredentials
$wc.Credentials = $cred
$src = $wc.DownloadString("http://localhost/Reports/Pages/Folder.aspx")
echo "finished pre-cache of SSRS site"
```
***code ends above***

***batch file ssrs_master.cmd calls .ps1 and outputs to file***

powershell.exe -noprofile -executionpolicy RemoteSigned -file c:dbascriptsSSRSRecycleSSRSRecycle.ps1 > C:DBAScriptsSSRSRecycleoutput.txt

***code ends above***

***SSRSRecyle.xml, import this to create an event driven scheduled task***
***Note ‘ReportServerTempDB’ & ”MSSQL$UAT’ will be different you can find these from the event log or work them out quite easily***

2012-03-09T14:26:20
YourLogin

true
<QueryList><Query Id=”0″ Path=”Application”><Select Path=”Application”>*[System[Provider[@Name=’MSSQL$UAT’] and (EventID=5084)]] and *[EventData[Data and (Data=’COMPATIBILITY_LEVEL’)]] and *[EventData[Data and (Data=’ReportServerTempDB’)]]</Select></Query></QueryList>

domainuser
S4U
HighestAvailable

IgnoreNew
true
true
true
false
false

true
false

true
true
false
false
false
P3D
7

c:dbascriptsSSRSRecyclessrs_master.cmd

***Code ends above***

***Custom XML event filter as used by the task above***
***Note ‘ReportServerTempDB’ & ”MSSQL$UAT’ will be different you can find these from the event log or work them out quite easily***

*[System[Provider[@Name=’MSSQL$UAT’] and (EventID=5084)]] and *[EventData[Data and (Data=’COMPATIBILITY_LEVEL’)]] and *[EventData[Data and (Data=’ReportServerTempDB’)]]

***Code ends above***
Reply
- Pavel Pawlowski says:
  
  2012-03-13 at 08:14
  
  Hi, you are rigth abou the Application Domain recycling. however the situation related to configuraiton changes can be predicted and you can schedule the changes or immediatelly query the RS service.
  
  The situation of hi memory presure and resources outage it not possible always predict but can be avoided with prooper reports design etc.
  
  Only the recycling after the specified period of time is regular and this is the issue if there is heavy use of the RS during business hours than the timer start since last recycle and next recycle can occure anytime during the business hours. Thi can cause that som euser will comlain about long runnning report even it is handled by triggered event.
  
  So the only way how to avoid regular recycling during business hours is to recycle and query the RS service out of bussiness hours so it is reqdy when the business day starts.
  
  Related to the premissions to start/stop the service. I do not see the issue as any administrative scripts you launch on the machine, launch under some higher priviledged account, so the script for the RS recycling can be launched under higher priviledged service account as other task. Also it is possible to grant stop/start only this particular service.
  
  Anyway, your approach is also good approach and each solution has it’s pros an cons.
  
  Reply
ITFC says:

2012-03-12 at 13:06

Hmm didn’t like my XML! Let me know how and it I can output it.

Reply
- Pavel Pawlowski says:
  
  2012-03-13 at 07:59
  Hi, you can put your XML code inside of
```
<pre class="brush: xml;"> </pre>
```
  Reply
SSRS 2008 – dzień drugi « Takietam says:

2012-03-23 at 13:40

[…] Rozwiązanie 1: wydłużyć czas recyklingu oraz automat odpalany w nocy, który za nas będzie udawał pierwszego użytkownika: http://www.pawlowski.cz/2011/07/solving-issue-long-starting-report-ssrs-2008/ […]

Reply
Ryan Smith says:

2012-04-10 at 14:06

Pavel, I’ve used this on a Native Mode Reporting Services instance and it works great. I’m having the same problem however in a Sharepoint Integrated Mode environment and I can seem to get the script to work with this mode. Does it work for Sharepoint Integrated Mode?

Thanks!

Ryan

Reply
- Pavel Pawlowski says:
  
  2012-04-10 at 19:28
  Hi,
  
  I didn’t tested this with service in SharePoint integrated mode, but I don’t see any problem using it even i SharePoint integrated mode.
  
  The only thing you will have to modify is the URL to be queried. Don’t know whether it is enough to query eg. URL of some SharePoint library with reports, or whether it will be necessary to open some kind of report.
  
  First I would try to query a library.
```
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//mysharepointsite.com/Tests/ReportServerTest/Reports/Forms/current.aspx")
```
  If this will not be enough, the I would create a simple report with eg. single text box or query any of existing reports in any library.
  
  If I will have a little more time I will test this directly on some SharePoint site.
  Reply
Mark Roworth says:

2012-06-21 at 10:37

Thanks Pavel. You’ve solved a long standing problem. Many thanks.

Reply
Mark T says:

2012-07-31 at 23:00

Thank you Pavel, this is an interesting issue we’ve run into lately.

Reply
timo says:

2012-09-10 at 13:40

Hi,

Any idea why I get this error? It does work anyway, the reports page opens quickly after this has run but there is this timeout:

PS C:WindowsSystem32> $wc = New-Object system.net.webClient
PS C:WindowsSystem32> $cred = [System.Net.CredentialCache]::DefaultNetworkCred
entials
PS C:WindowsSystem32> $wc.Credentials = $cred
PS C:WindowsSystem32> $src = $wc.DownloadString(“http://localhost/reports_ssrs
jt/Pages/Folder.aspx”)
Exception calling “DownloadString” with “1” argument(s): “The operation has tim
ed out”
At line:1 char:26
+ $src = $wc.DownloadString( <<<< "http://localhost/reports_ssrsjt/Pages/Folder
.aspx")

Reply
- timo says:
  
  2012-09-10 at 14:24
  
  solved: Microsoft.ReportingServices.UI.FolderPage+InsufficientPermissionsToRoot: User ‘TESTDOMAINSSRS’ does not have required permissions. Verify that sufficient permissions have been granted and Windows User Account Control (UAC) restrictions have been addressed.
  
  Reply
  - Pavel Pawlowski says:
    
    2012-09-10 at 18:18
    
    Great, that you have figured it out. 🙂
    
    Reply
Gezar says:

2012-09-19 at 15:41

Fantastic! At long last you gave me a neat solution for a problem I had for a long time…

Reply
timo says:

2012-10-03 at 13:28
I have it like this so I don’t have to worry about first requests as my script does that for me. Whenever the SSRS service recycles, it will do the warmup:

1. Open powershell and run
Set-ExecutionPolicy RemoteSigned
Get-ExecutionPolicy

Should return “RemoteSigned”. After this you can run powershell scripts locally

2. Create a folder c:ssrs for example
3. Create a file ssrswakeup.ps1 with these Pawel’s lines in it

$wc = New-Object system.net.webClient
$cred = [System.Net.CredentialCache]::DefaultNetworkCredentials
$wc.Credentials = $cred
$src = $wc.DownloadString(“http://localhost/reports/Pages/Folder.aspx”)

Check the $src http address from SSRS Configuration Manager

4. Create wakeup.cmd with this:
powershell -command “& ‘c:ssrsssrswakeup.ps1’ ”

5. Open Task Scheduler and create new:

Name: SSRS Wakeup
Account: add an account with permission to open url in $src
Trigger:
On an event -> Custom -> Edit Event Filter-> XML (@Name is your service name)
```
<QueryList>
  <Query Id="0" Path="Application">
    <Select Path="Application">*[System[Provider[@Name='SQL Server Reporting Services'] and Task = 0]]</Select>
  </Query>
</QueryList>
```
Task = 0 means startup.

Actions Start a program:
c:ssrswakeup.cmd

Other helpful:

– SSRS Recycle time is in minutes (1440 min = 24h)

C:Program FilesMicrosoft SQL ServerMSRS10_50.SSRSJTReporting ServicesReportServerrsreportserver.config
<RecycleTime>1440</RecycleTime>

– SSRS Logfile C:Program FilesMicrosoft SQL ServerMSRS10_50.SSRSJTReporting ServicesLogFiles
Reply
- timo says:
  
  2012-10-03 at 13:30
  
  Step 5:
  
  5. Open Task Scheduler and create new:
  
  Name: SSRS Wakeup
  Account: add an account with permission to open url in $src
  Trigger:
  On an event -> Custom -> Edit Event Filter-> XML (@Name is your service name)
  
  *[System[Provider[@Name=’SQL Server Reporting Services’] and Task = 0]]
  
  Task = 0 means startup.
  
  Reply
timo says:

2012-10-03 at 13:31

Damn,

<blockquote cite"

*[System[Provider[@Name=’SQL Server Reporting Services’] and Task = 0]]

“>

Reply
timo says:

2012-10-03 at 13:31

Damn,

<blockquote cite="

*[System[Provider[@Name=’SQL Server Reporting Services’] and Task = 0]]

“>

Reply
timo says:

2012-10-03 at 13:54

I cant use it 😦

<blockquote cite ="

*[System[Provider[@Name=’SQL Server Reporting Services’] and Task = 0]]

“>

Reply
Don says:

2012-10-30 at 21:18

Dear Pavel, Dear Everyone,

Quick question for you guys. Does this problem occur for each individual user of SSRS, hence leading to fine-tune the solution for each user (sounds unlikely to me) or is it just a ‘global’ warm up that needs to performed with one single user (which is what I do believe).

Thank you for your time and answers,

Don

Reply
- Pavel Pawlowski says:
  
  2012-10-30 at 21:41
  
  Hi,
  
  as you thought, the issue is related to the global warm up after the application domain is recycled. So it is necessary to do at least one request to the SSRS service. During the first request SSRS initializes all internal structures and caches and this takes the time.
  
  Reply
  - Don says:
    
    2012-10-30 at 22:43
    
    Thank you Pavel for such a quick answer,
    
    Excellent articles !
    
    Reply
Peter says:

2012-11-08 at 10:55

Hi Pavel,
i used your script, but i get this error, could you help me please with this?
Windows PowerShell Copyright (C) 2009 Microsoft Corporation. All rights reserved.
PS C:WindowsSystem32WindowsPowerShellv1.0> C:AdminSSRSrecycleSSRSrecycle. ps1 WARNING: Waiting for service 'SQL Server Reporting Services (MSSQLSERVER) (ReportServer)' to finish starting... WARNING: Waiting for service 'SQL Server Reporting Services (MSSQLSERVER) (ReportServer)' to finish starting... Exception calling "DownloadString" with "1" argument(s): "The operation has tim ed out" At C:AdminSSRSrecycleSSRSrecycle.ps1:6 char:26 + $src = $wc.DownloadString <<<< ("http://server/Reports/Pages/Folder.aspx") + CategoryInfo : NotSpecified: (:) [], MethodInvocationException + FullyQualifiedErrorId : DotNetMethodException

Reply
- Pavel Pawlowski says:
  
  2012-11-08 at 11:10
  
  Hi,
  
  The issue is that the Report Manager didnt’ start withing the timeout which the WebClient has. Unfortuantelly it is not possbile to set longer timeout for the WebClient without creating a new class and overide parent methods. But even this error has been thrown the initialization of SSRS started and after some while the the report response should be normal.
  
  To avoid this error it could be possible to handle the errors inside the PowerShell script, or use the HttpWebRequest class GetResponse instead of WebClient. The HttpWebRequest class has the possibility to setup the timeout. You can take a look on samples on MSDN – HttpWebRequest.GetResponse Method and HttpWebRequest.Timeout Property.
  
  Reply
  - Peter says:
    
    2012-11-08 at 13:09
    
    thank you for your response
    
    Reply
Rob van Meeuwen says:

2012-11-29 at 16:59

I tried your solution, but I get:
Task Scheduler failed to launch action “powershell.exe -noprofile -executionpolicy RemoteSigned -file c:dnmRestartSSRS.ps1” in instance “{f9449b39-316a-4c3a-b9b7-e1e8cdbdfec7}” of task “SSRS Recycle”. Additional Data: Error Value: 2147942523.

Running the “powershell.exe …..” using the ‘run’ command works.
Any idea?
Thanks in advance!

Reply
- Pavel Pawlowski says:
  
  2012-11-30 at 07:12
  
  Has the user under which you are trying to launch the scheduled task administrative rights? The powershell in the example is running with elevated administrative rights, so the account uner which it is executed has to have administrative rights on the computer.
  
  Reply
  - Rob van Meeuwen says:
    
    2012-11-30 at 09:18
    
    Yes, I am using the domain admin account which has full admin rights on the server. I also checked the ‘Run with highest privileges’.
    
    Reply
    - Pavel Pawlowski says:
      
      2012-11-30 at 21:10
      
      Can you post all the details from the Event Log related to this problem?
      
      Reply
      - Rob van Meeuwen says:
        
        2012-12-03 at 12:04
        
        First I get:
        Task category: Action failed to start
        Description: Task Scheduler failed to launch action “powershell.exe -noprofile -executionpolicy RemoteSigned -file c:dnmRestartSSRS.ps1” in instance “{46caf0b2-50df-49ad-90d6-0f4f9fd203ae}” of task “SSRS Recycle”. Additional Data: Error Value: 2147942523.
        Then I get:
        Task category: Action start failed
        Description: Task Scheduler failed to start instance “{46caf0b2-50df-49ad-90d6-0f4f9fd203ae}” of “SSRS Recycle” task for user “customerdomainAdministrator” . Additional Data: Error Value: 2147942523.
        
        Reply
      - Rob van Meeuwen says:
        
        2012-12-06 at 11:21
        
        Hi Pavel,
        
        Do you have any idea in which direction I have to look to solve this problem?
        Thanks,
        Rob
        
        Reply
        
        Pavel Pawlowski says:
        
        2012-12-06 at 12:34
        
        Hi, sorry for late answer, but the only thing I have found it seems to be related to the user credentials and/or UAC.
        
        Your account under which you are launching the script is local account or domain account? If it is a domain administrator You could try to add this account directly to the local administrators group.
        
        Other option could be granting the user to start/stop the SSRS service even without elevating administrative rights.
        For this you can ceck this techned thread Using AD to allow a user to start/stop a service
        
        Rob van Meeuwen says:
        
        2012-12-07 at 13:40
        
        Hi Pavel,
        Thanks for your reply. I found the solution. The task manager will not run powershell on 64bit properly. You need to point directly to the 32 bit version, which is located on C:WindowsSystem32WindowsPowerShellv1.0powershell.exe. (see also http://social.technet.microsoft.com/Forums/en-US/ITCG/thread/e298d613-47b8-4492-92d1-0b55cc8497c1)
        So in the ‘edit action’ window (of the scheduled task) I entered this in the ‘Program/script’ textbox, and the other parameter you mentioned, in the ‘Add arguments’ textbox. Now it works!
Amardeep Bains says:

2012-12-20 at 11:28

Hi Pavel i have tried your solution but cant get it to work on my server the script runs fine and restarts reporting services no errors when opening the reports folder, i have scheduled the recycle time to 1500 and have the job running at 2 in the morning everyday. But still the first report of the day takes nearly 6 minutes and then reports after less than a minute. Any ideas i have tried subscribing to a report in the reports folder which fires every 20 mins but that doesn’t make a difference either.

Reply
- Amardeep Bains says:
  
  2012-12-20 at 13:08
  
  Does it make a difference that the report is embedded into a web site and we are not running the reports direct from SSRS?
  
  Reply
  - Pavel Pawlowski says:
    
    2012-12-26 at 08:56
    
    Hi, this should not make any difference as even the report is integrated in the web page, the SSRS instance hast to be accessed.
    
    Reply
- Pavel Pawlowski says:
  
  2012-12-26 at 09:04
  
  Hi, I would try instead of querying the report manager, the report itself directly. If this will help, than your problem will not be related only to the SSRS application domain restart, but can be related also to the caching on the source database side etc.
  
  You can also review the [dbo].[ExecutionLogStorage] to check execution details of the report processing like Data Retrieval Time, Processing or Rendering times.
  
  Reply
  - Amardeep Bains says:
    
    2012-12-27 at 11:09
    
    I have queried the [dbo].[ExecutionLogStorage] and noticed that the TimeDateRetrieval is very high when running the first report of the day, processing time is about the same all the time and time rendering is sometimes higher on the first run.
    
    Reply
    - Pavel Pawlowski says:
      
      2012-12-27 at 14:51
      
      So it seems to be the report query issue. Once queried, the data are being cached and following executions are fetched from the DB engine case. When not used for longer period of time, the data are flushed from the DB engine cache.
      
      You should take a look on the query execution plan and try to fine tune the query to lower the execution time.
      
      If it is not possible, then depending on the report you can try to utilize report caching or report snapshots. See MS TechNet Performance, Snapshots, Caching (Reporting Services) for details.
      
      Reply
      - Amardeep Bains says:
        
        2012-12-27 at 15:37
        
        Could i run the query’s which populate the reports via a sql job in the mornings thus caching them so when users run the reports they shouldn’t get the wait?
        
        Reply
        
        Pavel Pawlowski says:
        
        2012-12-27 at 15:40
        
        Better to use the Caching mentioned in previous answer (this will do the job of executing the query and cache the data so sub sequential execution will use the cache) and of course.. if possible, fine-tune the query.
        
        Amardeep Bains says:
        
        2012-12-27 at 15:42
        
        As the reports use parameters where the values change is caching still an option?
        
        Pavel Pawlowski says:
        
        2012-12-27 at 17:05
        
        Yes, it is possible to cache parameterized reports but it depends on the report. See the link I’ve posted in previous answers.
        
        Anyway first I suggest to focus on optimizing the query whenever possible.
Dolly says:

2013-06-13 at 20:31

Hello Pavel,
The SQL job is a great workaround but Is there a more permanent solution to this problem like applying any changes to the settings on the server side as supposed to creating and maintaining the sql job.

Thanks.

Reply
- Sangeeta says:
  
  2013-08-02 at 16:57
  
  Hello Pavel,
  
  I tried to implement the solution you have mentioned in this article to get past the same issue we are having in our SSRS report server.
  
  I have scheduled a task to execute the Power shell script everyday at 3:00 am and set the recycle time to 1500 minutes in the reportserverconfig.
  
  The last step in the Power shell script where we redirect to the download string is not successful…It always shows the logon form to authenticate the first time.
  
  Here is the script in the powershell I am executing;
  
  Stop-Service SQL Server Reporting Services (SSRS_DEV)
  Start-Service SQL Server Reporting Services (SSRS_DEV)
  $wc = New-Object system.net.webClient
  $cred = [System.Net.CredentialCache]::DefaultNetworkCredentials
  $wc.Credentials = $cred
  $src = $wc.DownloadString(https://ssrsdev.contractlandstaff.com/ReportMgr_DEV/Pages/Folder.aspx)
  
  I wanted to know if there is something I can do so that the logon form would not pop up.
  I will appreciate your response.
  
  Thanks,
  Sangeeta
  
  Reply
subash says:

2013-08-12 at 12:25

Hi,
I have a weird problem, I have about a suite of 200 reports and only one of them takes a lot of time to even load (in BIDS as well), I can not understand why only this one takes such a log time, I has 5 datasets and 4 parameters(which is not much)

Thanks,
subash

Reply
- Pavel Pawlowski says:
  
  2013-08-12 at 12:34
  
  Hi,
  
  Does the long starting report uses a DropDown for parameters? If there is a quite a lot of rows in the drop down parameter, than the report will start much longer. Event the query for the parameter returns the records immediately.
  
  You can also take a look into the ExecutionLogStorage table in the Report Server DB for details what consumes the time.
  
  Reply
PhongCao says:

2013-08-30 at 04:33

Hi Pavel,
Thanks for your solution, it’s great to work around with SSRS first start issue. It’s really effect in Report Manager that it’s load better in first time. But when I apply to actual project with MVC app using ReportViewer to show report, it’s still take long time in the first time. I dont know why or I’m wrong in some thing? I also tested instead of calling to ReportManager url, I tried to call to an empty report (e.g: “http://localhost/Reports/Pages/Report.aspx?ItemPath=%2fReports%2fReportEmpty”) but still take long time when firsttime load report in my web app. Do you have any idea?

Reply
kiquenet says:

2013-09-07 at 11:23

Great posts and comments.

Any sample ps1 script about modify RecycleTime value in the rsreportserver.config ?

Reply
Automation Scripting PowerShell: Servicios, SSRS, etc « El TecnoBaúl de Kiquenet says:

2013-09-17 at 12:16

[…] Otra referencia, utilizando tarea Scheduled Task: http://www.pawlowski.cz/2011/07/solving-issue-long-starting-report-ssrs-2008/ […]

Reply
SSRS: Keeping It In Warm Standby Mode | dba + Doof = dbaDoof says:

2013-11-12 at 23:31

[…] for the slow initial startup time. As nicely described in Pavel Pawlowski’s blog post (http://www.pawlowski.cz/2011/07/solving-issue-long-starting-report-ssrs-2008/), the complete solution involves increasing the time between resets and also scripting out an […]

Reply
FabiNova says:

2014-05-19 at 06:00

Hi Pavel,
I have a Windows desktop application which has the same problem, and I have used a similar approach to fix it. I have made a batch file which calls the stored procedure which is used in the application. It takes about a minute to display all the result in cmd. Assuming that SSRS has loaded all the required settings, I run the application, but still it takes a lot of time to load and then gets faster on next load.
How couldnt this work?

Reply
- Pavel says:
  
  2014-05-19 at 07:30
  
  Your issue will be related to cold cache. When you ring your application for first time ti take long as SQL Server has to load all thedata from disk. Subsequent starts runs the queries agains cached data on the SQL Server side and therefore runs faster.
  
  I would focus on optimizing tasks the stored proc is doing to minimize the stored proc runtime.
  
  Reply
Configuring the RecycleTime SSRS Setting | Adam Kreul | Blog says:

2015-05-02 at 14:34

[…] 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 […]

Reply
Graesham says:

2015-08-14 at 13:20

can you please tell us how to do it in sharepoint, which url to use. seems like the script is just stop and restarting ssrs but its not loading the string maybe because I don’t know which string to use in sharepoint

thank you in advance

Reply
- Pavel says:
  
  2015-08-19 at 17:06
  
  if it is SSRS 2008 intgegrated in sherepoiit, then you can restart the service as mentioned here. Then I would create a “blank” report (eg. with only some label) and launch the report URL with the method mentioned here. Browsing the SharePoint library will not help as SharePoint will not utilize SSRS until report rendering is requested.
  
  Reply
Peter says:

2015-08-18 at 14:49

So I have followed everything in this very cool article and I still find first report exports to PDF take around 7 seconds. Subsequent ones take 1 second. So I created a subscription to run early morning after the services are restarted thinking this would help…nope, the first person to run the report in the application, takes 7 seconds to execute and export to PDF. What am I missing? Is the export to PDF done differently in an app vs a subscription? What else can I do to speed up the first export to PDF of a report?

Reply
- Pavel says:
  
  2015-08-19 at 16:55
  
  It does not looks like issue of the cold SSRS Startup. In case of SSRS startup mostly causes much higher initial report startup times.
  
  Based on the symptoms it looks like a DB caching related. Please check the SSRS report execution log for detail about report executions. You will find information about times of data retrieval, processing, rendering etc.
  
  Reply
  - Peter says:
    
    2015-08-19 at 18:03
    
    Will do but the report is NOT being cached…I don’t think I have any reports being cached
    
    Reply
    - Pavel says:
      
      2015-08-19 at 18:31
      
      It is not about reports being cached. its about a buffer pool on the DB Engine Side. When the first report is being launched, it may be that the DB engine does not have the necessary data in memory. Then in this case it needs to fetch the data from disk which is slow. Then subsequent calls to the report fetch the data from DB Engine memory, which is quick.
      
      Also it can be related not only to the buffer pool, but also related to plan cache. The query plan can be put off cache after some period of time. Then first call can be much slower when complex plan is being generated. Subsequent call fetch the plan from the plan cache memory and are more quicker.
      
      If the above is the reason, it will be easily to identify in the report execution logs.
      
      Reply
      - Peter says:
        
        2015-08-19 at 18:44
        
        Ahhh ok that makes sense. I have the log file after the services restarted this morning…can I send to you?
        PS-I am also noticing an error in the report, which is odd, because the subscription succeeds and generates a report correctly
        
        Reply
        
        Pavel says:
        
        2015-08-19 at 18:53
        
        the execution statistics are not located in the error log, but in the ReportExecution Log.
        
        It is stored in the Report Server database in the [dbo].[ExecutionLogStorage] table and supported also with set of views [dbo].[ExecutionLog], [dbo].[ExecutionLog2], [dbo].[ExecutionLog3] (depending on the SSRS version).
        
        There are fields like:
        ,[TimeDataRetrieval]
        ,[TimeProcessing]
        ,[TimeRendering]
        ,[AdditionalInfo]
        
        Additional info contains more granular information base on individual data sets in XML format.
        
        Peter says:
        
        2015-08-19 at 19:02
        
        Ok I have the view results open and I can clearly see a report running slow, then fast…now what?
        
        Peter says:
        
        2015-09-10 at 19:40
        
        Sorry to keep bugging you, but I can clearly see a report running slow, then fast…now what do I do?
        
        Pavel says:
        
        2015-09-10 at 20:15
        
        So, where is the difference, when you see those values from the SSRS Log?
glen says:

2015-09-10 at 17:19

This works perfectly. THank you very much for posting a very good solution!

Reply
Gavin says:

2016-01-13 at 12:35

Thank you, this worked well for me. If this issue had remained outstanding, it would have seriously affected the user perception of our new SSRS implementation. So thank you again.

Reply
kostyanius says:

2016-07-21 at 15:34

Hi, guys.
Does this solution work for 2014 version?

Reply
- Pavel says:
  
  2016-08-05 at 08:01
  
  HI,
  
  Yes, it works on SQL 2014 as well.
  
  Reply
Peter says:

2016-12-22 at 15:45

Ahoj Pavel,

Thanks a lot for your advice. It helped a lot 🙂

Reply
mark goldin says:

2017-02-13 at 20:19

I am using your script. The job runs with no problem. But the first report takes about 30 sec to start. Any idea?

Thanks

Reply
- Pavel Pawlowski says:
  
  2017-02-13 at 20:49
  
  Are you running the script manually or scheduled? If Scheduled, are you running it with elevated admin privileges?
  
  Reply
Dharuman says:

2017-09-29 at 14:45

Hi Pavel,

I am using your script. The job runs with no problem. But the first report takes about 20 sec to start. Any idea? am using WebApi

Thanks

Reply
Dharuman says:

2017-09-30 at 09:44

I am using your script. The job runs with no problem. But the first report takes about 30 sec to start and using WebApi. Any idea?

Thanks

Reply
Dharuman says:

2017-09-30 at 12:07

HI pavel,
how to pass webapi parameters in script..?

Reply
suhas bothe says:

2017-11-02 at 13:21

Hi,
My schdeuled task starts but it goes in infite loop.Please help

Reply
Anthony says:

2018-05-03 at 06:36

Please settle a debate with my coworker. By pointing at the source download string does this actually load all the reports into cache? I’m saying no, i just loads the settings back into cache. Coworker says it needs to loads all the reports into cache or your solution won’t work. Reason being, currently we have over 100 reports that we have to manually launch every morning or each initial report will be delayed. Thoughts?

Reply
- Pavel Pawlowski says:
  
  2018-05-03 at 07:22
  
  The problem being solved here is a delay of the SSRS after application domain is being recycled. The app domain recycling causes that SSRS needs to load its initial settings and reinitialize the internal stuff. After the app domain is being recycled even empty report is taking long time to load. And this solves that problem.
  
  If you have huge reports which are taking long for first run, then it is different story and is related to optimization on the DB side for the report queries or eventually caching the reports in the report server temp db. Mostly if first execution takes long (even it is not first report launched after the app domain is being recycled) it is caused by the report queries, that data for the report are no longer in the cache on the DB server or complex query plan is no longer in plan cache. Than the first execution takes long as the db server generates plan and fetches data from disk. This can be easily verified from the report execution log, where you have detailed statistics about individual phases of report execution.
  
  Reply
Pavan says:

2018-06-05 at 10:12

when i ran the given command with admin privileges, getting empty o/p. Is this expected, how to know reports being pre-loaded. or am i missing something here

$wc = New-Object system.net.webClient
$cred = [System.Net.CredentialCache]::DefaultNetworkCredentials
$wc.Credentials = $cred
$src = $wc.DownloadString(“http://localhost/reports/”)

Reply
Rod Foulcher says:

2021-01-02 at 03:55

Thanks Pavel,

Am using your solution successfully (with a few minor mods – see Timeout problem under my name if you are interested).

Rod

Reply
MSSQL Reporting Server slow on first connection - Boot Panic says:

2022-02-03 at 18:10

[…] more explicit instructions please read this […]

Reply
JerryC says:

2022-05-13 at 19:43
for what its worth. the report server config file needs an additional a tweak.
c:\Program Files\Microsoft SQL Server\MSRS13.MSSQLSERVER\Reporting Services\ReportServerrsreportserver.config
```
1500
        1000000
```
if sql server needs memory, it will steal it from reporting services, thus triggering a recycle.
i chose 1000000K=1 gig of memory as for me this is enough memory to handle all my ssrs needs.
I have 16 and 32 gig machines each running sql server 2016 and ssrs. And POS. this additional setting solved all my delay issues.
Reply
- JerryC says:
  
  2022-05-13 at 19:50
  
  the 1500 is recycletime, the 1000000 is WorkingSetMinimum the post striped out my less than and greater thans
  
  Reply
- JerryC says:
  
  2022-06-27 at 21:12
  
  RecycleTime 11500
  WorkingSetMinimum 1000000
  
  posting stripped the tags
  
  Reply
Al H says:

2022-10-05 at 16:34

Pavel,

Thank you for your solution. Unfortunately, it does not fix the problem for me.
I ran the script as an Administrator in PowerShell. I added a few Start-Sleep steps as I was getting an error when calling the report.

Stop-Service „SQLServerReportingServices“
Start-Sleep -Seconds 5
Start-Service „SQLServerReportingServices“
Start-Sleep -Seconds 30
$wc = New-Object system.net.webClient
$cred = [System.Net.CredentialCache]::DefaultNetworkCredentials
$wc.Credentials = $cred
$src = $wc.DownloadString(„http://localhost/Reports/Pages/Folder.aspx“)
Start-Sleep -Seconds 90

After the script completes, I open a simple report which queries the Report Server tables (and normally runs in 1-2 seconds), and it still takes 60+ seconds to complete, after the restart.

I am using SQL Server 2019, SSRS 2019 on Windows Server 2019 Standard, so I wondered if there was a different way to call the SSRS report, or something else that might need changed.

Thank you.

Reply
- Pavel Pawlowski says:
  
  2022-10-06 at 15:47
  
  Hi,
  
  don’t have a SSRS 2019 at hand to test, but might be that on 2019 it is not enough to fetch the report manager page.
  
  I would suggest to put URL of some simple report on the DownloadString. Technically downloading a report should definitely force all the backend stuff of SSRS to be initialized.
  
  Reply
SSRS: First report is slow: Reloading Appdomain says:

2022-11-03 at 14:56

[…] the powershell restart script at […]

Reply
Daniel says:

2023-11-01 at 10:52

Thanks Pavel, this works very well with Microsoft SQL Server Standard (64-bit)

Reply