Slow report renderings and start up of report manager

  • I'm trying to find answers to problems with the speed of reporting services renderings/formatting. 

    I'm running RS 2000 SP2 with SQL 2000 (SP4) on the same server, windows 2003 with IIS v 6.

    Reports take WAY to long to display when the SQL behind them is relatively speedy.  I've got two issues with the speed I've been trying to address:

    1. Sometimes the web service appears to have to start.  So I set up an application pool just for reporting services and configured it to only recycle worker processes once a day at 5:00 AM.  My understanding was that this would keep the worker processes active and the long start delays would disappear except the first time Report Manager was used after the 5:00 am recycle.  This does not appear to be the case.  When it is slow starting it can take over a minute while IIS loads the RS manager - not a report just the manager itself.

    2. All reports take longer to execute than is acceptable.  Even reports that the SQL returns in milliseconds and may only have 10 records can take over 1 second to display.  I never see a report come up quick enough that the green working symbol is not seen.  For large reports (for example a 20,000 row one that has one grouping that has about 6,000 individual groups), it can take close to a minute for RS to render the report consistently when teh SQL runs to gather the data in about 9 seconds.  It takes seconds to open one collapsed detail section of the table after the report has completely rendered.

    I am rolling out RS and everyone likes the features, but the speed HAS to be corrected or I'll be putting some other reporting tool in.

    Also, it looks like my server has PLENTY of free memory and it makes me wonder if there are some configurations that need to be set. I also run several other small web apps on this server that are not slow like RS.  They are using a separate application pool. 

    Any help would be greatly appreciated.

     

  • A few questions for you.

    • Have you monitored both the client and the server during execution?  Does the client churn?  If so, then it could be that the browser is having issues trying to render the html.
    • When you hav a 6k+ line report, does it take long to go from page to page? 
    • Have you changed any of your logging settings (in RS) to make them more verbose so that you my see something in them.
    • I've not looked but I would assume the most verbase (probably called trace) level of logging within RS should show you when the query has been executed, then when the rendering of is starting and ending.  This could be a good place to get some baselines.
    • What's your DB allocation like for the RS temp db?  If it's not large (large enough to handle your data volumes) then each time you run these long reports it may be having to allocate further disk to the files which will slow the inserts into the temp tabes, and hen slow the rendering back to you. 
    • Further to point above, I thought I saw something (new setting) in SP2 that let's you 'cache' to disk (ie in filesystem) rather than DB, this may be worth looking into in case the overhead of writing to file system is less than th eoverhead of writing to the DB.
    • Does your IIS have html compression turned on?  If so, this oculd make things slower (at both ends) as it has to compress, and the client decompresses, the html before rednering can occur.
    • Nothing showing (error wise or otherwise) in your event logs?  Nothing in the system log, maybe showing that the system is trying to gain authentication but needs to make multiple attempts to get it fromt he domain controller?

    I would definitely recommend trying to break this into sections, and then reviewing/baselining each with logs (where possible).  So look at the SQL execute time (for you running the query in QA but also put tracing on to monitor the same query when executed via RS).  Look at the SQL status (file sizes etc) looking for possible synamic expansion when running reports (in particular the RS temp DB).  Lastly look at the RS logs to see if you can identify any longer running pieces of the execution task.

    Lastly, although a report may be only 10 rows, via a browser (even over the intranet at 100/1000 Mb) I wouldn't expect it to take < 1 sec to render (under 2s, yes probably).  I doubt IE can render the html involved that fast.  If you're comparing QA to RS, it's an unfair comparison as QA just presents the values.  Although i've not seen under the covers, I'd be assuming the overhead in what gets sent back to the client (Query analyser in this case) is little more than the actual bytes required to display the characters.

    Steve.

  • Forgot to ask re: slow Report Manager startup, what App Pool do you have it running under?  Have you checked the Performance tab of this app pool to see whether the worker threads are shut down after 20 minutes (default).  If these are shut down then it might take .net some time to recompile and start up Report Manager.  There's probably an elegant way (ie correct way) to do this but in a case of desperation you could write a script tha uses IE to hit report manager (just the main page) every 15-30 minutes, which should be enough to keep the threads alive.

    Steve.

  • 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.

  • Correction on #9.  It akes 1-2 seconds not 102 seconds.

  • 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. 

    The missing seconds would be the time it takes for the client to display the information. Nothing you do on the server will change that. This would be down to client hardware, software patches, display resolution, etc...

    To reduce the TimeProcesing, do as much work as you can on the SQL side. EG: Instead of using an expression to join two fields together in a textbox, join the fields in the query. I've found that the more expressions and dynamic logic you put in the report the longer it will take to run.

    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.

    Reporting services will still use caching which is typical of a server application. This allows it to respond quicker to subsequent requests for the same information.

    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.

    Presuming it's not already, I'd be interested in timings for this report done in ASP.NET instead of ASP. The extra delay could just be the overhead of the .Net framework.

    From my use of Report Manager I've noticed that it can take 5-10 seconds to initially load and then, depending on the complexity of the report, an extra few seconds or for the first execution of the report.

     

    --------------------
    Colt 45 - the original point and click interface

  • I do understand what you are saying about the more logic - the longer it takes.  I have found experiemetnally some things that take a long time.  In report paramteres - if you check allow null values it adds a lot of time to the report.  I haven't a clue as to why.   Have you found anything that has a good list of do's and don'ts for RS? 

    The two of the things that bother me most I guess is that very small reports without much formatting still take a long time ( longer thatn users are willing and should have to wait -- 1-2 seconds) and that after the report has finally rendered if the user does a next page or expands a collapsed region (that may have 20 detail lines in it) it takes multiple seconds.  This should be instantaneous once the report is displayed the first time. 

    What is it actually doing when you expand a region or next page?  If I knew that maybe I could track down the slowness if it's something on my end or that I coudl affect.

  • It would also be interesting to know what you row count and byte sizes are for the reports that are taking a while.  And how do these stack up against a report like the sample 'Territory Sales Drilldown'.  On my machine, this report returns all of the ~2600 rows to the client (prob report property set to 'fit on single page if possible' mixed with the groupings, anyway, i can't make it page).  It's about 17.5 kb in size, takes <1 sec to retrieve, process and render (all logged times) yet it takes longer than this to present to the client (IE).  I'd have to agree with Phil that a lot of this will be limited by the browsers ability to render the html (and probably a heavy amount of jscript).  For whatever reason, the expansion and contraction of the groups is not a fully client side event, it does a postback (of sorts) to the server - most likely so that RS can pickup the event happening and action it (if you set something to happen on open/close of the grouping). A quick test on my laptop shows my IE spiking to ~15% CPU for each and every click on the +/-, this is also definitely a round-trip event as the asp worker processes gets some processor time also.  I tried mucking around with setting the support for Javascript in the device settings (for HTML3.1 and 4.0 'devices') but it doesn't seem to make that much difference.

    Just out of interest, what's the display time like when executing the reports in IE on the server itself?

    Steve.

  • Just my simple testing it takes as long to page and open groupings on the server as it does my desktop. 

    I have been monitoring the W3WP.exe process more and watching what it does as I run reports.  I have two W3WP.exe processes running most of the time - one for RS and one that is the shared app pool for the intranet web apps running on this same server.  Their pool is still using default settings.  I create a seperate pool for RS to experiement around with the settings and not affect those other production apps.  The pool for RS has used up to 1/3 G of memory but the server still has 1 G of real memory left.  As I run the large reports, it churns at 45-80% CPU(after the right amount of time it would take take to actually execute the SQL query to get the data) while memory usage does slowly inch up(sometimes is takes up to 200 MB of more memory while rendering a report).  After it is finished and rendered - the W3WP process does not release the extra memory it took while running the report.  Paging a report causes the same CPU (45-60% of W3WP.exe) but it sometmes RELEASES memory while it is paging the report and about half of the time when it is finished paging the memory usage is lower than when it began.  The report with groupings take several seconds longer to page than a similar report without groupings(althought the report with groupings has at least twice as many rows in the dataset).  I can't find a pattern except that when rendering a report for the first time, memory either stays the same or goes up.  When paging, it flucuates and sometimes ends up with less memory allocated (by 100 MB).  Both these reports do have a lot of textual data.

    After playing around more with the very small reports I wonder if it's not got some problem with the green circuler reporting executing "thingy ma jig".  None of the reports ever display without displaying this green informational icon first.  Will RS ever dispaly report without displaying this green informational first?  Maybe it's slowing down the display on the very small very fast reports and I have two problems.  One with the large reports and a seperate issue with the informational green thing slowing down the presentation of the small ones.

  • By the way, I found the compression settings in IIS and none of them are set.

  • re: the green "I'm processing" thing, i think you can avoid this by using webservice or URL access to the reports - but this doesn't help much if you *want* to use report manager.

    Have you looked at the bytes that are generated by each report, and then being sent to the client (ie network utilisation)?

    Steve.

  • I monitored my desktop while rendering the report and the network had one blip as expected immediately before the rendering.  It did not last long enough to be the entire reason for the slow response.  I'm not a network expert and will continue the monitoring to see if I'm missing something, but right now it's not high on my suspect list.

    After continuing to monitor W3WP.exe yesterday evening I've decided that it must have something slowing it down that I can't point to.  The CPU utilization goes to 50-80% but I can't find anything else that is being constrained.  And I would think that CPU would go even higher if it were churning on something.  The idle process is still getting a fair amount of CPU that is left that W3Wp.exe is not getting.  The server is only running some internal webapps and Reporting Services.  The disk Queue lengths are nonexistent, there is still free real memory available (it's a 2 G machine and 1 G is still free), there are spikes in hard page faults while the CPU is high but the disk queue lengths do not go up so it must be handling them.  The network utilization doesn't appear to me to be a problem.  I've monitored the servers that the actual data is coming from for the reports but that piece only takes a couple seconds at the beginning of the report generation and the W3Wp.exe is actually almost idle during that portion of the report generation.  I've monitored the ReportServer and ReportServerTEMPDB using profiler during report generation and although its hitting them quite frequently, that is very fast.  With as much free memory as the machine still has, these DBs should be completely held in memory. 

    I just can't come up with a reason why it delays the way it does.  If it were CPU starved, it should peg the CPUs, memory is available, disk queue lengths look good.  What else is there to look at?

     

     

  • "With as much free memory as the machine still has, these DBs should be completely held in memory. "

    Not necessarily, only if all the data in the database is being accessed frequently will it be likely for DB be completely in memory. It wouldn't make sense for the data to be held in memory if it's not being used.

    "I've monitored the servers that the actual data is coming from for the reports but that piece only takes a couple seconds "

    One thing I forgot in my earlier post. How complex is the query that you're running and does it reside in the report, or in a stored procedure? We've noticed a large reduction in time if the report is getting data directly from the database table. It also goes without saying that there is also a difference between, SQL in the report and SQL in a stored procedure.

    For our main reports they are rendered via the web service and then emailed out using a C# console app. There are about 800-900 reports emailed out each night. For a couple of our reports, we added an extra step to the SQL Agent job to pre-populate a de-normalised work table before the report executes. Then the report uses a single parameter in the web service call to extract data from the work table for the report. This cut the overall processing time by about 75%.

     

    --------------------
    Colt 45 - the original point and click interface

  • Actually, I just took a look at the counts for today and we're up over 1100 reports all rendered and distributed in about 20mins. So I'm pretty happy with the performance

     

    --------------------
    Colt 45 - the original point and click interface

  • What OS are you running?  Are you running RS SP2?  What are your IIS configurations?  Do you have a seperate applicationPool for RS and is it set for web farm?  I'massuming data is coming from different servers than one running reportign services.

Viewing 15 posts - 1 through 15 (of 20 total)

You must be logged in to reply to this topic. Login to reply