SQL Reporting services: First call is very slow

Question

I've installed a SQL Reporting server (2008 R2), with some reports. But I've some performances issues.

The first call of the day to the server(going on the report interface by example), is VERY slow(something like 30-45seconds at best).

The report generation is then "fast"(1-2 seconds).

The next calls to the server are always fasts until the next day. I've the impression that it loads a lot of thing in the memory. But what can takes 30-45 seconds to be loaded in memory??? And how to load it only once?

The server is good enough(quad core, 8GB of ram, never near its capacity for now).

What is the problem? How can I resolve this ?

Thoses reports will be launched only 4-5 times in a week, so they will always be slow if I can't change this. And since it's available for customer, I just can't make them understand this(and the report is called through a website, so I risk to have timeout).

Thank you very much

Chibcha

The execution plan is being kicked out if it's not being used for a time. – Vitriform

Answer 1

It seems to be an SSRS issue. There is nothing wrong with your report.

It's "normal" that SSRS takes more time to load the first time you access it after a long time of inactivity. The issue is caused by the way how SSRS works and SSRS regularly restarts application domain after a 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.

This blog show's a workaround for the situation

Alumroot

I'm currently trying this solution, I will see tomorrow if it work :) – Chibcha

:) I was really happy this morning, because it worked! Do you know why SSRS needs regularly restarts the application domain? – Chibcha

@Alumroot This link no longer works. Has the work-around been re-posted anywhere? – Sialagogue

That blog is the best solution but his script needs modification. Instead of hitting Folder.aspx, you should be hitting an actual page, see my response below: – Antipodal

@SChalice- I didn't find that I had to hit an actual item. The original script works as advertised. – Coracle

@Diego: though this post is too old but it is helping me a lot, could you please tell me if I set some timer in config file.. like default 12hours, then how domain restart cycle will happen? I mean is, If some user is accessing the report in between 12 hours then the restart cycle will be reset from 0 minutes?? Or mandatory it will get restart after 12 hours even if the user is using the reports in between?? – Carrousel

2022 - This solution still works: SSRS 2014 – Pharsalus

Answer 2

Here is the powershell script that I wrote to fix the problem. It is setup as a task to run every 1:00am:

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/Report.aspx?ItemPath=***NAME OF HOME PAGE***")

Antipodal

Answer 3

The best solution I could come up with was to issue a 'curl' command to the http page of the report using via windows batch command in a windows service. This ran up the page(s) every morning before the users came in.

Don't have access to the code anymore (job was a while ago) but this question shows how to use curl:

http://blogs.plexibus.com/2009/01/15/rest-esting-with-curl/

Sarmatia

Answer 4

As Diego said, SSRS has some issues. The first call it is slow, regarding your server configuration. I recommend you the following config to add in rsreportserver.config (located if you don't know in C:\Program Files\Microsoft SQL Server\MSRS10.MSSQLSERVER\Reporting Services\ReportServer\ )

If you want to increase the max memory used by SSRS: (which means 7 GB)

<WorkingSetMaximum>7000000</WorkingSetMaximum>

If you want to improve the first call, you can set (in minutes)

<RecycleTime>4320</RecycleTime>

Actually SSRS has a reset (recycle) time in which cleans its buffer. By default its setted at 720 min (12h) so thats why if you open a report every morning it actually load very slow. As you need you can set the recycle time higher (2-3 days). I don't recommend a higher time because the buffer will fill up and you will get only blank pages, so you will have to manually restart Reporting Services.

Claussen

Answer 5

It may be completely unrelated to SQL Server. Try to see if is not the code sign revocation list check issue, see Fix slow application startup due to code sign validation

Cerecloth

I tried to put those two registry, but it didn't help, this morning I tested, and same problem. – Chibcha

Answer 6

I converted the main query and the dropdown controls on the page to load from stored procedures, it made a difference of say 5 sec in the loading process.Avoid using any inline queries.

Hairtail

Answer 7

The blog (http://www.pawlowski.cz/2011/07/solving-issue-long-starting-report-ssrs-2008/) worked very well. In my case I am using SQL Server 2017 Reporting Services. I only had a problem when calling "DownloadString", it is not possible to connect to the remote server. I added a sleep time using the "Start-Sleep" cmdlet. Another change made is that the address I need to preload is the reporting web service, not the portal. The complete solution was as follows:

    Stop-Service "SQL Server Reporting Services"
    Start-Service "SQL Server Reporting Services"
    $wc = New-Object system.net.webClient
    $cred = [System.Net.CredentialCache]::DefaultNetworkCredentials
    $wc.Credentials = $cred
    Start-Sleep -Seconds 30
    $src = $wc.DownloadString("http://localhost/ReportServer?/MyReport&Param=Title&rs:Format=IMAGE")

Then I created the scheduled task from the command line:

schtasks /create /tn "SSRS Recycle" /ru Administrator /rl highest /np /sc daily /sd 11/07/2023 /st 02:00 /tr "powershell.exe -noprofile -executionpolicy RemoteSigned -file "C:\scripts\SSRSRecycle.ps1"

The last recommendation that Pavel Pawlowski makes on his blog is to set the value of "RecycleTime" to 24 hours inside "C:\Program Files\Microsoft SQL Server Reporting Services\SSRS\ReportServer\rsreportserver.config" (you need administrative privileges to edit it): <RecycleTime>1440/RecycleTime>

Johnathanjohnathon