SQL Reporting Service Slow on first report

Hi,

I have SQL Server 2008 R2 installed on Server 2008 R2 and I’m finding that the reporting service when it loads up the first report of the day is rather slow. After the first report has been generated if it is in constant use it will remain fast. Is there any way to speed up the process when it has been sad idle for a while or to stop it from going idle?
EverymancomputersAsked:
Who is Participating?
 
ValentinoVConnect With a Mentor BI ConsultantCommented:
Another, probably more elegant, option is to increase the RecycleTime in the rsreportserver.config.  By default this is set to 720 minutes (12 hours).

Ref: http://technet.microsoft.com/en-us/library/ms157273.aspx
0
 
BembiCEOCommented:
The first initial access is slower anyway. After the first access, the RS loads the results into the cache and it looks faster after that.

Especially if you run complex reports, which need processiong time.

For complex reports, you may enable the preprocessing of the resports. (Properties of the Report in RS Web) Means the reports are not generated on the fly but in the background. This way the reports are in the cache before the user accesses them. The lack of this is, that the reports are not exactly on the fly anymore, but you can set a time scope, when they are refreshed...
0
 
Alpesh PatelAssistant ConsultantCommented:
You can cache the report or create snap shot for that to speed up report from Report Manager.
0
Problems using Powershell and Active Directory?

Managing Active Directory does not always have to be complicated.  If you are spending more time trying instead of doing, then it's time to look at something else. For nearly 20 years, AD admins around the world have used one tool for day-to-day AD management: Hyena. Discover why

 
Nico BontenbalCommented:
This might help:
http://blogs.msdn.com/b/buckwoody/archive/2009/06/24/sql-server-best-practices-autoclose-should-be-off.aspx

If autoclose was off already you might be able to schedule a report every hour to keep things awake. You can use a report schedule if you have SQL Standard or higher, with Express you can use a vb script and a scheduled task.
0
 
jogosCommented:
If you profiler trace the queries of your first run then you can see in which queries the time goes.  Compare it to a second run.

If time difference is in reading the data from your reporting database (report definition) or from your database which contains the data you know where to look for improvement.

If your statistics are out of date, you have missing or fragmented indexes  then you read to much into your cache and the frist time that takes time ... afterwards while data is in cache it goes quicker as long as that data stays in cache (so regularly used).
0
 
ValentinoVBI ConsultantCommented:
To avoid the slow "Woohoo, I'm the first report of the day!" load, you need to "warm up" your report server.

Have a look at the following article where the issue is described: http://blogs.msdn.com/b/psssql/archive/2010/07/04/revisiting-an-old-ssrs-performance-topic-again.aspx

It also contains a vbs script that can be used to time the initial warm-up.  The bottom part of that script (starting at "Getting ready to instantiate the RS web service") can be used to warm up your server. Schedule it through the SQL Agent at some time in the morning, about 5 minutes before the first user will request a report.

I'll copy the crucial part here as reference:

'Getting ready to instantiate the RS web service
ts = Now()
Dim rs As New RS2005.ReportingService2005
Dim creds As New Net.NetworkCredential
creds = Net.CredentialCache.DefaultCredentials
rs.Credentials = creds
rs.Url = "http://localhost/reportserver_r2/reportservice2005.asmx"
rs.ListChildren("/", False)
'Instantiated the RS web service

Open in new window

Pay attention to the URL, you'll probably need to customize that to your situation.
0
All Courses

From novice to tech pro — start learning today.