CODEWITHSUNDEEP
sql-serverreporting-servicesssrs-2008
J4N
J4N

Reputation: 20717

SQL Reporting services: First call is very slow

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

Upvotes: 21

Views: 68199

Answers (7)

jcallejas
jcallejas

Reputation: 31

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>

Upvotes: 0

Arif Imtiaz
Arif Imtiaz

Reputation: 1

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.

Upvotes: 0

Diego
Diego

Reputation: 36146

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

Upvotes: 29

SChalice
SChalice

Reputation: 510

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

Upvotes: 3

Mihai
Mihai

Reputation: 657

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.

Upvotes: 1

Remus Rusanu
Remus Rusanu

Reputation: 294237

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

Upvotes: 0

Preet Sangha
Preet Sangha

Reputation: 65496

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/

Upvotes: 1

Related Questions