SSRS - OutOf MemoryException - is there a limit to the number of rows that can be displayed

Question

I have created an rdl doc that points at a proc that returns 90 000 rows and I am getting an out of memory exception. Is there a limit to how many rows the report projects can handle?

Currently I have changed the proc that drives my report to just do a select Top 90 000. My specs are to be able to create a report with 120 000 rows. My report is a matrix.

I swear last week I generated a report that has 106800 rows in it but now all of sudden I can't.

I have written a rendering extension and here is part of the exception when I step into the code.

eInfo: 2/12/2009 12:03:53 PM prairieFyre.ReportActions.RenderReport: Error rendering report Microsoft.Reporting.WinForms.LocalProcessingException: An error occurred during local report processing. ---> Microsoft.ReportingServices.ReportProcessing.ReportProcessingException: An unexpected error occurred in Report Processing. ---> System.OutOfMemoryException: Exception of type 'System.OutOfMemoryException' was thrown. at System.IO.MemoryStream.set_Capacity(Int32 value) at System.IO.MemoryStream.EnsureCapacity(Int32 value) at System.IO.MemoryStream.Write(Byte[] buffer, Int32 offset, Int32 count) at System.IO.BinaryWriter.Write(String value) at Microsoft.ReportingServices.ReportProcessing.Persistence.IntermediateFormatWriter.ReportServerBinaryWriter.WriteString(String stringValue) ....

Answer 1

I don't think there is a limitation, other than your hardware configuration. If your on a 32-bit machine the worker process that is processing this has less than 2 gigabytes of memory to work with, probably closer to 1 gigabyte when you factor in kernel mode memory. If your going to be serving up large reports like this, you probably need a 64-bit setup with at least 4 gigs of memory or more on the box. This setup will allow the worker process to allocate more than 2 gigs of usable memory to complete these large requests without issue.

If a hardware upgrade is not an option you can also consider these alternatives.

• You said the report was a matrix report so it sounds like you are not displaying all that data to user but aggregating it. Could you pre-aggregate some of this data in the database and then just use SSRS for the display?

• Since the report is nothing more than an XML file, build the report XML string from within SQL server or by using some script or process. This may be a lot of work.