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

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

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***")