posted 7/27/2012 by MMilligan - Views: [4257]
This totally killed me for longer than I'll ever admit. I sat with DBAs running profiler traces, looking at execution logs, and running various tests. Turns out it's Reporting Services 101 (someone else's words, not mine.) As soon as my colleage said the words "Interactive Page Size" I slapped myself in the forehead. Anyway, I wanted to share this so no one else goes through what I went through.
Symptoms: Report takes too long to render or never renders. Data retrieval through stored procedure or inline SQL is short (most of time spent in rendering.) BIDs throws system.outofmemoryexception error when running the report in BIDs. Report renders when filtered or top 1000 added to query; but, too much data causes it to either run indefinitely or return no data and a little yellow triangle with an exclamation point appears in the bottom left status bar in Internet Exploder with the words Error on Page.
I wish the System.OutOfMemoryException said something like, "check your that interactive page size height property is not set to 0." I wish there were more clues about what was going on when running on a server. We were only talking about 200K rows of data with some drill-down capability. Even if the server had to render hundreds of pages it should have eventually worked or at least come back with some sort of error or something. (Maybe we missed something here, I don't kinow... I'm not a DBA; but, I played one in a webinar once.)
From: Troubleshooting Reports: Report Performance
As a user pages through a report, the report processor combines data and report layout information for each report page and passes the page to the report renderer. For a report that has no page breaks, the whole report must be processed before the user can view the first page.
A soft-page break renderer, such as the HTML viewer, automatically handles paging for you. You can override this automatic behavior and set the report to be one page by setting the Report property InteractiveHeight to 0. For hard-page break renderers, you must add page breaks manually. For more information about types of renderers, see Understanding Rendering Behaviors (Report Builder 3.0 and SSRS).
Verify that InteractiveHeight is not 0 and that it is set to some reasonable page size, for example, 8.5 in. Add page breaks to report items or Tablix groups to help organize the report into pages. This reduces the amount of data that must be processed for each page. For more information, see How to: Add a Page Break (Report Builder 3.0 and SSRS).
---------------------------------------------------------------------------
More info:
SSRS InteractiveHeight = 0 and IE Script Performance (Connect suggestion)
Report Design: Best Practices and Guidelines