SQL Reporting Services performance on navigation pages.

I am experimenting with SQL Reporting Services on a machine with SQL Server 2005 loaded and deploying

reports to a different server. both servers are equivalent in resources.

In the browser on the Reports machine when I first go to the Reports address, it takes a very long time

to build and load the page. it then takes a long time for each drilled down page. Subsequent calls to the same

pages are very fast if done within a certain amount of time. This implies that there must be some form of

caching going on and that the cache expires after a period of time. I have seen in the RSReportserver.config

file that there are parameters relating to report caching, but they all appear to reference the actual reports being

generated. My issue is simply being able to build the start page and other navigation more quickly and to

keep it active so it does not have to perform that build each time.

Any information related to managing this situation would be very much appreciated..

Thank you, Ray

Teo, Thanks for this response, it sends me in a direction I had not yet been.

I now see that the SessionTimeout setting is set to 600 (10 minutes). is it OK to simply change that

setting in the table I would have assumed there might be a better utility or something that would handle that.

I saw another post that discussed some of this and it implied that the setting could be changed via an API or

even the rs.exe utility. I also thought it referenced the SessionTimeout in th RSReportserver.config file, but I

don't see it there. perhaps it can be added to override the default

I'm not sure that this is even what I really want to do. My issue is not that a given report takes too long, but

that it takes way too long to build the navigation pages the first time and I don't see any reference to that

in the ExecutionLog Table. I'm not clear on what all is occuring during this build phase that should take that long.

I'm on a pretty fast/solid network so it really shouldn't. don't know if you or anyone can shed any light on that for me.

bottomline is that my users want to be able to get to the reports on demand and I don't want them to

have to wait for the navigation pages.

ray

The right way is to call ReportingService2005.SetSystemProperties API. The quick and easy way is to change it directly in the database and restart iis. The SessionTimeout setting is not exposed in the management tools.

I don't see any reference to that in the ExecutionLog Table.

kind of what I thought.. thanks for the clarification..

I did find another thread that sort of addresses my issue of slowness on the first load. if you're interested take a look.

thank you, Ray

http://forums.microsoft.com/MSDN/ShowPost.aspx PostID=477143&SiteID=1

The SessionTimeOut parameter is definitely a remedy to the situation, but it doesn't really

help in identifying why it would take so long even to load the first page of the Reports Manager.

You asked if this was a report builder report, it is not, but even so, I don't have a problem recognizing

that a given report might be slow depending on the database structure and volume of data. To the contrary,

when I do actually run the report, it executes pretty fast.

The link I referenced in my previous post points to an explanation linking the problem to the way that

IIS starts it's process and it gives a possible solution to set up a call to the reportserver every few minutes, but

that seems like a band-aid solution, albeit maybe the only real answer at this point. I have other applications

using IIS and none of them has this issue. The pages load immediately, no matter how long it's been dormant.

this leads me to believe that reporting Services has some background activity going on that requires more

than meets the eye.

I don't seem to be alone in this (other posts seem to have a similar issue) and maybe it's because there are

2 physical machines involved, maybe my setup has some flaw I'd be interested in hearing about users that

don't experience this.

thx, Ray

This is probably the best answer there is on this. one thing to note though, I went ahead and set this up

on a single server environment (so we don't have the network and communications potential for slowness)

and the initial load is much faster. still takes approx 30 seconds, but that's a lot better than 2 minutes as before.

thanks for your time on this,

Ray

I will do that, but also remember, I'm concerned about the time it takes to initally load the

Report Manager. I do not, yet, have any complaint about the time required to actually

generate a given report. I know I can monitor the ExecutionLog to track that stuff.

I haven't been involved in this form for awhile, who is supposed to mark the correct answer button

I'm not sure there really is a correct answer, just general theory as to what to look for.

Ray

I ran into a similar problem, check this out:

http://www.sqlreportingservices.net/Ask/5536.aspx

Here is part of it:

=========================

The solution is pretty simple actually.

Microsoft does a great job of getting technologists excited about new technologies like the 'Web Gardens' in IIS 6.

The problem was two fold -- but all in IIS.

First, I had bumped the web garden setting up to '8' thinking that it would spread the processing out. Maybe if I was getting a gazillion hits a day, but the reality was that each worker process had to spin up. So if I started one browser and tried to run a report, it would spin #1 up. If I opened another browser, it would spin up #2 and so on.

Second was that I had the worker processes 'recycling' on idle. Which in a web garden of 8 would really cause delays. By the time I got to spinning up #7 or #8, #1 had already spun back down.... ARGHHHH.

Solution was to put worker processes back on '1', and turn off the recyling.

Now it stays up all the time, and responds almost instantly.

Good Luck.

=========================

See if it helps at all....

Dan Ribar

Dan,

This is more like it.

To clarify though, in your linked reference you mention that, on the Performance Tab, we should UNCHECK

Recycle I see nothing called recycle on that page, but the Idle Timeout appears to be what you are referring

to. I unchecked that and things work well. Do we know if that can cause other issues certainly for now,

this fixes my issue.

thx, Ray Dodson

Ray,

We've been running like this now for quite a few months in production and haven't even had to think about it again... so for us it has caused no problems. My take on it is that they designed the garden into things for scaleability. On the worst day I might generate 10,000 reports and haven't had to 'scale' yet......

Good luck.

Dan Ribar