Revision 0d8391a5-b0cb-4fcc-9e88-a65de0a03543 - Database Administrators Stack Exchange

I have been given the task of improving the performance of the first report connection each day. [Similar to this post][1]. 

We have a simple SSRS report that acts as a landing page. It has hyperlinks to subsequent report. 

I have tried: 

 - implementing the [powershell restart script][2]  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][3].

It seems the problem is that SSRS somehow treats these connections differently than if a real user hits the report. 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 get hit with 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 click "go" to the screen being rendered. 2 minutes!!! No wonder users are complaining. 

  [1]: https://dba.stackexchange.com/questions/118047/ssrs-report-slow-on-initial-query-of-the-day
  [2]: https://pawlowski.cz/2011/07/31/solving-issue-long-starting-report-ssrs-2008/
  [3]: https://stackoverflow.com/questions/28740951/ssrs-and-powershell-get-report-as-excel/28765315

Looking at my SSRS log files 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


    library!WindowsService_0!1bdf8!08/24/2017-08:00:19:: i INFO: Call to CleanBatch()
    library!WindowsService_0!1bdf8!08/24/2017-08:00:19:: i INFO: Cleaned 0 batch records, 0 policies, 0 sessions, 0 cache entries, 0 snapshots, 0 chunks, 0 running jobs, 0 persisted streams, 0 segments, 0 segment mappings, 0 edit sessions.
    library!WindowsService_0!1bdf8!08/24/2017-08:00:19:: i INFO: Call to CleanBatch() ends
    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, 0 persisted streams, 0 segments, 0 segment mappings, 0 edit sessions.
    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.Modeling.InternalModelingExceptionMicrosoft.ReportingServices.ReportProcessing.UnhandledReportRenderingException
    library!ReportServer_0-1!adb0!08/24/2017-08:16:51:: i INFO: Do not dump on: System.Threading.ThreadAbortExceptionSystem.Web.UI.ViewStateExceptionSystem.OutOfMemoryExceptionSystem.Web.HttpExceptionSystem.IO.IOExceptionSystem.IO.FileLoadExceptionMicrosoft.SharePoint.SPExceptionMicrosoft.ReportingServices.WmiProvider.WMIProviderExceptionSystem.AppDomainUnloadedException
    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, RoleGuid:8cc0ac85-40f7-4886-9dab-021519800418
    library!ReportServer_0-1!adb0!08/24/2017-08:16:52:: i INFO: Call to GetItemTypeAction(/CIA). User: BEEBLE\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: BEEBLE\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\RSTempFiles for temporary files.


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?