Thanks for the response. Let me answer your questions with the answers I do know.
1. During monitoring, I see CPU go up on server where Reporting Services resides and it appears that most of the CPU spike goes to a process W3WP.exe. This is a process associated with an application pool? Memory usage does not go up along with CPU as I would expect. Almost as if the application pool I have defined for reporting services needs more memory but is being constrained. Do you know if there are configuration items on how much real memory a pool is allocated? Desktop sees activity but nothing major. I wonder if I am monitoring desktop properly to see network traffic. I’ll do more work on this.
2. Yes, the longer the report, the longer it takes to page. It can take as long to page as I would have thought the whole report would take. For the long report in question it takes about 5 seconds to page to the next page or jump to a specific page. It also takes 4-5 seconds to open a collapsed grouping.
3. I have not turned on verbose logging to look at this. I have used it to look at a mail issue I had some time ago but will have to find the help on it again.
4. tempdb is not growing during the normal use of reporting services. It is defined at 128 MB and 32 MB log. Also reportServerTempDB is not growing (it has room to hold the data from the report). Each execution causes the ReportServerTempDB to grow in use(not size) by about 6 MB.
5. Report execution times are found in a table called ExecutionLog. The time it takes to actually see the report on the desktop is about 5 seconds longer than the total of the times it keeps; TimeDataRetrieval, TimeProcessing, and TimeRendering. For this report these times add up to about 6 seconds less than it actually takes to see the report. For smaller reports these times add up to about 3 seconds less than it actually takes to see the report. By far the greatest of the three is TimeProcessing. I haven't found any help on how to optimize anything based on these times - ie what causes longer processing time.
6.I'm not sure what I'd be caching. I should have enough memory on the machine to do all sorting in memory if it's all allocated right.
7.I've never set anything about HTML compression. Can you give me a pointer on where to find this setting and I'll check.
8.No errors in event logs. We are using Kerberos for our internal web app security to SQL and we are also battling occasional errors where this falls back to NTLM and therefore security fails. But I get no security errors on the reports and they do render properly.
9.at least one smaller report that was imbedded in an intranet application was changed to asp report because it appears instantaneous to the user using ASP and takes 102 seconds using ReportingServices with link to report.
10.I created an application pool dedicated to RS. It's name is ReportignServicesAppPool. I set it to recycle the worker threads at 05:00. On the performance tab of this pool I have unchecked the shutdown worker processes option and also the CPU monitoring is not checked. The request queue limit is still set at the default 1,000. I played around with changing the default idle time for worker processes and finally came up with the 5:00 AM refresh to make sure it occurred at the same time every day. The pool is set to run under Network Service.
But, these settings also appear at the application pool group level ( not on each individual pool). This is configured to shut down idle processes every 20 minutes and recycle every 1740 minutes. Which one takes precedence - the one defined specifically for the pool? Or is it a combination of both.