SSRS Report Slow on Initial Query – Performance Tips

reportingssrs

I am trying to fix an issue where an SSRS report is slow for the initial query of the day. I have tried using the solution described here:

Solving issue with first long starting report on SSRS 2008

…to no avail. I know that it seems to be an SSRS issue, but I am not sure if there is another workaround for this behavior. It uses Microsoft's ServerReport.Render method to render the report and dump it to a PDF.

Clarifications from comments:

I created a script like the one in the blog. I had that script run during off hours prior to the start of the business day. I also manually ran the script line by line in PowerShell and that did seem to alleviate the issue; however, that could have just been a placebo as the slowness was reported again by the QA assigned to this issue.

I have manually run the report and experienced the same level of slowness for the initial report query.

I do have a task that runs just before the start of business every day. I created the scheduler with the following line:

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"

Is the path case sensitive? My Scripts file was named Scripts. Also, do I need slashes in the path name?

Best Answer

Per the article you provided, give this a shot for another potential workaround:

  • On the Reporting Services server, modify the RecycleTime value of the rsreportserver.config file, and time this out so a 'recycle' at the app server level of RS occurs just before the start of the regular business day every day - like an hour or so before any production RS reports would typically be run.

  • Then setup a small dummy Subscription report to run every day at the same time—ensure it has enough time beforehand though for the RS app 'recycle' to complete. Running this non-production small dummy report as the first and initial report each day after the RS app server 'recycle' completes should ensure RS app server stuff loads up before any production reports are run, and production reports shouldn’t experience the problem.

This way your Reporting Services app server recycle operation happens every day at the usual and designated time per the value you set in the config file, then right after that happens (giving ample time for full completion) the dummy report runs before any production reports run for the day, and then every subsequent report for the next 24 hours runs as expected without needing to wait for the RS app server post 'recycle' load operations to complete. You also could leave out the PowerShell scripted solution altogether as well if this works.

Related Question