Memory Exception for Simple Report
I am trying to run an SSRS report. This is a simple report, just for displaying data from a table containing about 80K records.
No aggregation or data processing is performed on the report. There are about 50 columns along with 19 report parameters. I just need to display these 50 columns in a report (no pivot point).
It usually takes about 5 minutes to get the report on our development server (from peak hours). The same thing happens with our production server, but there users often get "Out of memory" exceptions and also report parameter criteria are not used (complaints I get from users).
I can filter the criteria locally without any problem, although it takes a long time to render.
-
Why does it take a long time to report, even if the report is simple?
-
The report works fine when I press F5 on VS 2008, but get out of memory exceptions from time to time when I click on the Preview tab.
-
Some column names have a "#" character. If I include such columns in a report, an "out of memory" exception is thrown (especially in preview mode). Is there any truth to this: not SSRS like column names with "#"? For example. my column name was "KLN #".
-
I created a non-clustered index on the table, but that didn't help me much.
-
What is the difference between running a report in preview mode and removing F5 on VS 2008? It's ok when I press F5, although it takes 5 minutes, but the problem is with view mode.
There is not much room to redesign (as this is a direct report), perhaps only I can remove the report parameters.
Any suggestion would be appreciated.
source to share
Why does it take a long time to render ...?
I created a Nonclustered index on the table, but that didn't help me much.
Because (AFAIK) SSRS will build an in-memory memory model before rendering. Be aware that SSRS will take three steps when generating a report:
- To get data.
- Create an internal model by combining report and data.
- Sending the report in the appropriate format (preview, html, xls, etc.)
You can check the ExecutionLog2 View to see how long each step takes. Step 1 is probably already fast enough (in seconds) so the added Index doesn't solve the bottle neck. Probably steps 2 and 3 are time consuming and require a lot of RAM.
SSRS doesn't like C # column names? my column name was KLN #.
As far as I know, this shouldn't be a problem. Removing this column was most likely enough to make the report runnable again.
There isn't much to reverse engineer (as this is a direct report), for example, except that I can remove the report parameters.
SSRS just isn't right for this. Thus, there is no real "solution" for your problem, only alternatives and workarounds.
Workarounds:
- As @glh mentioned in his answer, increasing the amount of RAM available for SSRS can "help".
- Requiring the user to filter data using a parameter (i.e. not letting the user select all those rows, only the ones they need).
- Schedule the report at a quiet moment (when there is enough RAM available) and cache the report .
Alternatives:
- Create a small custom application that reads from a database and outputs Excel.
- Use SSIS which (I thought) is better suited for this kind of task (data transformation and migration).
- Reimagine your setup. You haven't provided the context for your report, but you may have an XY problem . Perhaps your users want the entire report but only need a few key lines, or perhaps they are only using it as a backup mechanism (for which there are better alternatives) or ...
source to share
In addition to the already posted answers and preview issues in the Report Designer or Report Manager, there is another possible solution: avoid too much data on the first page of the report!
This can be done paginated into small numbers of records, i.e. custom groups with page breaks or sometimes automatically (see done_merson's answer) or adding a simple title page. These solutions are especially useful during the design phase and if you plan to provide the report results in Excel or PDF anyway.
I had a similar case with out of memory exceptions and never returned reports with a simple report and its dataset containing about 70k records. The request was completed after about 1-2 minutes, but neither the report designer nor our developer SSRS Server 2008R2 (Report Manager) was able to show the resulting report preview. Finally, I suspected the HTML preview was a bottleneck and avoided it by adding a title page with a simple text box. The following execution of the report took about 2 minutes and successfully showed an HTML preview with a title page. It took another 30 seconds to render the full result in Excel.
Hope this helps others as this page is still one of the top posts if you're looking for SSRS out of memory exceptions.
source to share