Ray Dodson


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




Re: SQL Reporting Services performance on navigation pages.

Teo Lachev


SSRS always performs database caching (aka user sessions) by saving the report intermediate format in the report database. This is why subsequent calls to the report using the same parameters are very fast. The SessionTimeout setting in the ConfigurationInfo table in the ReportServer database controls the lifetime of the user sessions.

I suggest you take a look at the ExecutionLog table as a first stop to troubleshooting performance issues. It will tell you how much time the Report Server spents in data retrieval, processing, and rendering the report.







Re: SQL Reporting Services performance on navigation pages.

Ray Dodson

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






Re: SQL Reporting Services performance on navigation pages.

Teo Lachev

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.

Is this a Report Builder report If not, there must be a record in the ExecutionLog table. Antother way is to use the SQL Server Profiler to find how long the query takes to execute. The rest is spent in report processing and rendering. How many pages does the report have





Re: SQL Reporting Services performance on navigation pages.

Ray Dodson

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





Re: SQL Reporting Services performance on navigation pages.

Ray Dodson

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





Re: SQL Reporting Services performance on navigation pages.

Teo Lachev

There are many things that needs to happen when the report server initializes. To see what's going on during initialization, use an external trace monitor, such as SysInternals DebugView.

Assuming Windows Server 2003, the IIS applicaton pool hosting SSRS will time out after 20 min of inactivity. You can go to the application pool properties and bump up that value as needed. In Windows XP, you can open the machine.config file and increase the timeout of the asp.net process.






Re: SQL Reporting Services performance on navigation pages.

Ray Dodson

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





Re: SQL Reporting Services performance on navigation pages.

Teo Lachev

Again, I'd use DebugView to see when the request hits the server and monitor the server activity. There could be other things happening outside SSRS. Also, take a look at the ExecutionLog table to find how much time is spent in data gathering, processing and rendering of the report.






Re: SQL Reporting Services performance on navigation pages.

Ray Dodson

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





Re: SQL Reporting Services performance on navigation pages.

dribar

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





Re: SQL Reporting Services performance on navigation pages.

Ray Dodson

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





Re: SQL Reporting Services performance on navigation pages.

dribar

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