3

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:

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

  1. The scheduled cache refresh at 08:00:19 & 08:10:19
  2. Followed by my report request about 8:15:01
  3. 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-131479929006508632'
appdomainmanager!DefaultDomain!adb0!08/24/2017-08:15:01:: i INFO: Creating a new AppDomainLifeCycleManagementEntry for AppDomain id '3'
appdomainmanager!DefaultDomain!adb0!08/24/2017-08:15:01:: i INFO: Appdomain STARTED: id='3'; name='ReportServer_MSSQLSERVER_0-1-131479929006508632'
appdomainmanager!ReportServer_0-1!adb0!08/24/2017-08:15:08:: i INFO: RS authentication mode is 4; effective ASP.NET authentication mode is Windows. vdir=/ReportServer.
appdomainmanager!DefaultDomain!adb0!08/24/2017-08:15:08:: i INFO: Appdomain:3 ReportServer_MSSQLSERVER_0-1-131479929006508632 initialized (#1).
appdomainmanager!DefaultDomain!adb0!08/24/2017-08:15:08:: i INFO: SetInitialized AppDomain id '3': initialized = 1
webserver!ReportServer_0-1!adb0!08/24/2017-08:16:51:: i INFO: Reporting Web Server started
library!ReportServer_0-1!adb0!08/24/2017-08:16:51:: i INFO: SQLDump flags: ReferencedMemory, AllThreads, SendToWatson
library!ReportServer_0-1!adb0!08/24/2017-08:16:51:: i INFO: MiniDump flags: DataSegs, UnloadedModules, ProcessThreadData
library!ReportServer_0-1!adb0!08/24/2017-08:16:51:: i INFO: Dump on: Microsoft.ReportingServices.Diagnostics.Utilities.InternalCatalogExceptionMicrosoft.ReportingServices.
library!ReportServer_0-1!adb0!08/24/2017-08:16:51:: i INFO: Do not dump on: System.Threading.ThreadAbortExceptionSystem.Web.UI.ViewStateExceptionSystem.OutOfMemoryExceptionSystem.
library!ReportServer_0-1!adb0!08/24/2017-08:16:51:: i INFO: Minidump location: C:\Program Files\Microsoft SQL Server\MSRS13.MSSQLSERVER\Reporting Services\Logfiles
runningjobs!ReportServer_0-1!adb0!08/24/2017-08:16:51:: i INFO: Running Requests Scavenger timer enabled: Next Event: 60 seconds.  Cycle: 60 seconds
runningjobs!ReportServer_0-1!adb0!08/24/2017-08:16:51:: i INFO: Running Requests DB timer enabled: Next Event: 60 seconds.  Cycle: 60 seconds
resourceutilities!ReportServer_0-1!adb0!08/24/2017-08:16:51:: i INFO: Reporting Services starting SKU: Developer
ui!ReportServer_0-1!adb0!08/24/2017-08:16:52:: i INFO: Installed SKU: Developer edition
ui!ReportServer_0-1!adb0!08/24/2017-08:16:52:: i INFO: Registering with Software Usage Metrics Product:SQL Server 2016, Role:Microsoft SQL Server Reporting Services,  etc etc
library!ReportServer_0-1!adb0!08/24/2017-08:16:52:: i INFO: Call to GetItemTypeAction(/CIA). User: MYDOMAIN\prm.
library!ReportServer_0-1!adb0!08/24/2017-08:16:53:: i INFO: Catalog SQL Server Edition = Developer
crypto!ReportServer_0-1!adb0!08/24/2017-08:16:54:: i INFO: Initializing crypto as user: NT Service\ReportServer
crypto!ReportServer_0-1!adb0!08/24/2017-08:16:54:: i INFO: Exporting public key
crypto!ReportServer_0-1!adb0!08/24/2017-08:16:54:: i INFO: Importing existing encryption key
library!ReportServer_0-1!adb0!08/24/2017-08:17:02:: i INFO: Entering StreamRequestHandler.ExecuteCommand - Command = StyleSheet
library!ReportServer_0-1!adb0!08/24/2017-08:17:02:: i INFO: Exiting StreamRequestHandler.ExecuteCommand - Command = StyleSheet (success)
library!ReportServer_0-1!adb0!08/24/2017-08:17:06:: i INFO: Call to GetItemTypeAction(/CIA). User: MYDOMAIN\prm.
library!ReportServer_0-1!adb0!08/24/2017-08:17:06:: i INFO: RenderForNewSession('/CIA')
library!ReportServer_0-1!adb0!08/24/2017-08:17:07:: i INFO: Using folder C:\Program Files\Microsoft SQL Server\MSRS13.MSSQLSERVER\Reporting Services\blahblahblah

As each of the various workarounds I have tried so far don't seem to work...
How can I force SSRS into doing this before a real user attempts to run the report?

Improve this question
0

2 Answers 2

Reset to default
3

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?

Improve this answer
2
  • Of course, apologies. Have edited above.
    – Jerb
    Commented Sep 12, 2017 at 15:42
  • I tested this and it works really well. And so I've marked your answer as correct. Thanks :)
    – Sir Swears-a-lot
    Commented Sep 13, 2017 at 1:34
1

I found a workaround, but not one I'm especially happy with. We managed to use our Website monitoring software to hit the SSRS report server, and it seems to do just enough that SSRS web component starts up. The first user of the day now gets a response within 4 seconds.

I have spent hours trying to get powershell to do this for me, and got very close. So I'll leave this here. If someone else can solve the last piece of the puzzle and make this work I'll credit them with the answer:

$username = "<Domain\user>"
$password = "<Password>" | ConvertTo-SecureString -asPlainText -Force
$cred = New-Object System.Management.Automation.PSCredential($username,$password)
$response = Invoke-WebRequest -Uri "http://<ServerName>/ReportServer/Pages/ReportViewer.aspx?%2f<ReportName>" -Credential $cred
$stream = [System.IO.StreamWriter] "D:\temp\Out.html"
try {$stream.Write($response.Content)} 
finally {$stream.Dispose()}

The key part missing is that SSRS uses the web-portal to respond.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.