SSRS: First report is slow: Reloading Appdomain

I have been given the task of improving the performance of the first report connection each day. Similar to this post.

We have a simple SSRS report that acts as a landing page. It has hyperlinks to subsequent reports.

I have tried:

• implementing the powershell restart script at 6:30am.

• configure report cache to refresh on a schedule every 10 min after restart

• Adding a subscription.

• scheduling a powershell script to export the report.

It seems the problem is that SSRS somehow treats these connections differently than if a real user opens the report through a web browser. Even though the report is run it doesn't appear to be handled in the same way as if a user is hitting it via the portal. And so.. the first user experiences a big delay.

Using the F12 debugging features in internet explorer, I managed to reproduce and capture what the browser is seeing. SSRS only took 4 seconds to produce and return the report, however in the browser it took 122 seconds from trying to open the URL to the screen being rendered. 2 minutes!!! No wonder users are complaining.

Looking at my SSRS log files ( Located in: C:\Program Files\Microsoft SQL Server\MSRS13.MSSQLSERVER\Reporting Services\LogFiles) it appears that the SSRS appdomain is restarting or reloading.

The Log excerpt below shows

• The scheduled cache refresh at 08:00:19 & 08:10:19

• Followed by my report request about 8:15:01

• I don't get the report back until about 8:17:06

Logfile:

```
library!WindowsService_0!1bdf8!08/24/2017-08:10:19:: i INFO: Call to CleanBatch()
library!WindowsService_0!1bdf8!08/24/2017-08:10:20:: i INFO: Cleaned 0 batch records, 0 policies, 0 sessions, 0 cache entries, 0 snapshots, 0 chunks, 0 running jobs, etc etc
library!WindowsService_0!1bdf8!08/24/2017-08:10:20:: i INFO: Call to CleanBatch() ends
appdomainmanager!DefaultDomain!adb0!08/24/2017-08:15:01:: i INFO: Registering AppDomain: type='ReportServer'; id='3'; Name='ReportServer_MSSQLSERVER_0-1-13147992900650863

I think I've been able to fix what I think is broadly the same issue.

First thing in the morning, if I go to our Report Server URL, it loads quick enough and I'm able to browse the menu straight away no problem. But as soon as I click on an actual report, there's that 2 minute wait before the report actually executes...

[Quick test... it was only 75 seconds for me this time, but it felt like longer!]

Running a report from a command line using the "RS" command seems to start up (wake up!) whetever part of the architecture isn't otherwise being started and the first report takes a normal amount of time to execute. I shall shedule that as a task and forget about it.

RS is a bit fiddly to use, since you need to script a bit of VBA to execute the report, but I already needed to work that part out for another job I had..

Edit: Added script below

I can't really take all the credit for this, nor cite references I'm afraid. It's cobbled together from 3 or 4 different resources already out there, and adapted to suit my needs (specifically, I wanted to generate a set of pdf exports for a range of inputs). And then simplified a little bit more for here:

Script file "RunReport.rss"

Public Sub Main()

  Dim format as string = "PDF"
  Dim fileName as String
  ' adapt the following variable for your report location
  Dim reportPath as String = "/{my file path}/{my report name}"

  Dim parameters(0) As ParameterValue

  ' Prepare Render arguments
  Dim historyID as string = Nothing
  Dim deviceInfo as string = Nothing
  Dim extension as string = Nothing
  Dim encoding as string
  Dim mimeType as string
  Dim warnings() AS Warning = Nothing
  Dim streamIDs() as string = Nothing
  Dim results() as Byte

  rs.LoadReport(reportPath, historyID)

  fileName = "output.pdf"

  results = rs.Render(format,  deviceInfo, extension, _
  mimeType, encoding,  warnings, streamIDs)

  ' Open a file stream and write out the report
  Dim stream  As FileStream = File.OpenWrite(fileName)
  stream.Write(results, 0, results.Length)
  stream.Close()

End Sub

Which is then executed from the command line with

rs -i RunReport.rss -s http://{my server name}/reportserver -t -e Exec2005

And in this case creates a file called "output.pdf"

Hopefully that may be of use?

StackExchange Database Administrators Q#183941, answer score: 3