SQL Reporting Service Slow on first report


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?
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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...
Alpesh PatelAssistant ConsultantCommented:
You can cache the report or create snap shot for that to speed up report from Report Manager.
Nico BontenbalCommented:
This might help:

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.
Creating Active Directory Users from a Text File

If your organization has a need to mass-create AD user accounts, watch this video to see how its done without the need for scripting or other unnecessary complexities.

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).
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.
ValentinoVBI 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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today

From novice to tech pro — start learning today.