Link to home
Start Free TrialLog in
Avatar of Everymancomputers
Everymancomputers

asked on

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?
Avatar of Bembi
Bembi
Flag of Germany image

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...
You can cache the report or create snap shot for that to speed up report from Report Manager.
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.
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).
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.
ASKER CERTIFIED SOLUTION
Avatar of ValentinoV
ValentinoV
Flag of Belgium image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial