Reporting Services SP1 Designer Cache Problem

Reporting Service SP1 provides an option to cache the data during
design time for preview, which seems to be making the preview to take
lot longer time than pre SP1.
This is what Reporting Services SP1 is doing:
During preview it is making a call to SQL and in between it is writing
into report data file, which is taking longer time just because it is
reading and writing into data file before even rendering.
Why can't it cache the data after it gets the entire data instead of
holding the sql connection or after rendering?
One of my reports is used to take 10 secs prior to SP1 now it is
taking 6 mins.
After turning of the configuration to false we started getting the
report in 10 secs.
Is there any resolution for this?
Thanks
RI have a couple of questions. Does this type of performance difference
occur for all reports or just this one? What is the structure of the data:
number of rows returned, etc. I would expect caching to take longer but not
in the magnitude you are describing.
The report is cached and rendered as we get the data (it is not buffered).
On the surface it seems quite in efficient to render the report and then
potentially round trip to the server again. Buffering is an issue since we
do not know the size of result set in advance.
--
Bruce Johnson [MSFT]
Microsoft SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"am_i_dotnet" <am_i_dotnet@.hotmail.com> wrote in message
news:ac7b2754.0407151240.2374c78b@.posting.google.com...
> Reporting Service SP1 provides an option to cache the data during
> design time for preview, which seems to be making the preview to take
> lot longer time than pre SP1.
> This is what Reporting Services SP1 is doing:
> During preview it is making a call to SQL and in between it is writing
> into report data file, which is taking longer time just because it is
> reading and writing into data file before even rendering.
> Why can't it cache the data after it gets the entire data instead of
> holding the sql connection or after rendering?
> One of my reports is used to take 10 secs prior to SP1 now it is
> taking 6 mins.
> After turning of the configuration to false we started getting the
> report in 10 secs.
> Is there any resolution for this?
> Thanks
> R|||There are around 20000+ records in this report and the report is in
tabular format.
I may not agree with you on "The report is cached and rendered as we
get the data". Because I observed the data file keep growing when I
tried to preview the report and report showed up only after the entire
data file created.
I know it is inefficient to make second round trip to server just to
cache the data but do we have any thoughts on using the data in the
report. I mean first render the report and cache the data
asynchronously from report data.
Thanks
R
"Bruce Johnson [MSFT]" <brucejoh@.online.microsoft.com> wrote in message news:<#dyED4qaEHA.3684@.TK2MSFTNGP09.phx.gbl>...
> I have a couple of questions. Does this type of performance difference
> occur for all reports or just this one? What is the structure of the data:
> number of rows returned, etc. I would expect caching to take longer but not
> in the magnitude you are describing.
> The report is cached and rendered as we get the data (it is not buffered).
> On the surface it seems quite in efficient to render the report and then
> potentially round trip to the server again. Buffering is an issue since we
> do not know the size of result set in advance.
> --
> Bruce Johnson [MSFT]
> Microsoft SQL Server Reporting Services
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "am_i_dotnet" <am_i_dotnet@.hotmail.com> wrote in message
> news:ac7b2754.0407151240.2374c78b@.posting.google.com...
> > Reporting Service SP1 provides an option to cache the data during
> > design time for preview, which seems to be making the preview to take
> > lot longer time than pre SP1.
> >
> > This is what Reporting Services SP1 is doing:
> > During preview it is making a call to SQL and in between it is writing
> > into report data file, which is taking longer time just because it is
> > reading and writing into data file before even rendering.
> >
> > Why can't it cache the data after it gets the entire data instead of
> > holding the sql connection or after rendering?
> >
> > One of my reports is used to take 10 secs prior to SP1 now it is
> > taking 6 mins.
> >
> > After turning of the configuration to false we started getting the
> > report in 10 secs.
> >
> > Is there any resolution for this?
> >
> > Thanks
> > R

Reporting Services MemoryLimit

We're having trouble with memory configuration in Reporting Services 2k5 SP1, running on Win 2k3 SP1. We have a very large report, which we've been running to benchmark performance.

First, here are the results of our testing:

Initially, the server had 512mb of RAM. When running the report, the w3wp.exe process used up to 400mb of RAM and Reporting Services usually timed out before delivering the report.


We increased the RAM in the server to 1024mb, and found we could run the report. It took around 02'30" to complete, and the w3wp.exe process peaked at 520mb of RAM usage. When we tried to run two instances of the report concurrently, RAM usage peaked around 800mb, and Reporting Services timed out before delivering the reports.


Increasing the server RAM to 1536mb, we found could run two reports at once. It took around 04'05" to deliver the reports, and the w3wp.exe process peaked at 920mb of RAM usage. When we tried running three reports concurrently, RAM usage peaked around 1,220mb of RAM and Reporting Services usually timed out before delivering the reports.


Lastly, we put 2048mb of RAM in the server, and we could run three reports at once. It took around 06'58" to deliver the reports, and the w3wp.exe process peaked at 1,280mb of RAM usage. When we tried running four reports concurrently, RAM usage peaked around 1,290mb and Reporting Services died with a System.OutOfMemoryException exception.

The pattern we noticed with the first three steps, was that Reporting Services isn't using more than 80% of the physical RAM available on the server:

400mb / 512mb = roughly 80%

800mb / 1024mb = roughly 80%

1,220mb / 1536mb = roughly 80%

1,290mb / 2048mb = roughly 63%

Following the directions in "Configuring Available Memory for Reporting Services" (http://msdn2.microsoft.com/en-us/library/ms159206.aspx), we tried increasing the MemoryLimit of both the Web Service and the Windows Service.

We changed the memoryLimit in machine.config to:

<processModel autoConfig="true" memoryLimit="120" />

and the memoryLimit in rsreportserver.config to:

<MemoryLimit>120</MemoryLimit>
<MaximumMemoryLimit>140</MaximumMemoryLimit>

It looks like the ReportServer is reading this, as when it is restarting, the ReportServer_*.log file records:

w3wp!library!1!11/10/2006-10:20:56:: i INFO: Initializing MemoryLimit to '120' percent as specified in Configuration file.
w3wp!library!1!11/10/2006-10:20:56:: i INFO: Initializing MaximumMemoryLimit to '140' percent as specified in Configuration file.

However, it doesn't go above this 80% threshold.

More concerning is the System.OutOfMemoryException exception described above. Following the suggestions in KB 909678, we've tried tweaking the MemoryLimit and there is clearly still plenty of RAM left available on the server. This is happening repeatedly when we try to run four instances of the report concurrently.

Are there other parameters we should be adjusting to ensure Reporting Services uses the available RAM?

Thanks,
Andrew

Hi

We've made no progress on this issue.

Has anyone else found that setting the memoryLimit in the processModel element of machine.config seems to have no effect?

Thanks,
Andrew

|||

The memoryLimit setting in machine.config is deprecated if you are running IIS6. You should define worker process memory limits in IIS metabase, or use IIS manager UI.

I am curious about two things:

1. Is the other 20% of memory unused, or taken up by other processes?

2. Is there a timeout setting that can be tweaked?

|||

Sorry if that wouldn't be exactly on topic, but...

Having a report that is running for 2 1/2 hours is an issue by itself. Considering that report is based on a set of stored procedures, I would instead work on tweaking these stored procedures as well as settings on the SQL server that is providing you with such timing! Data volume returned by the SSRS is not an issue, report design is.

|||

We faced similar problems...

make sure that all the data area id's in the tables are connected to each other on an inner join (respective relationships maintained) ...

That will stop the memory leak ...and improve perfomance drastically

|||Are you suggesting that the queries that the report runs off of use inner joins? Or something else? Please give more detail.|||

Hi Glen

02'30" = two minutes and thirty seconds, not two hours and thirty minutes.

Thanks,
Andrew

Reporting Services MemoryLimit

We're having trouble with memory configuration in Reporting Services 2k5 SP1, running on Win 2k3 SP1. We have a very large report, which we've been running to benchmark performance.

First, here are the results of our testing:

Initially, the server had 512mb of RAM. When running the report, the w3wp.exe process used up to 400mb of RAM and Reporting Services usually timed out before delivering the report.


We increased the RAM in the server to 1024mb, and found we could run the report. It took around 02'30" to complete, and the w3wp.exe process peaked at 520mb of RAM usage. When we tried to run two instances of the report concurrently, RAM usage peaked around 800mb, and Reporting Services timed out before delivering the reports.


Increasing the server RAM to 1536mb, we found could run two reports at once. It took around 04'05" to deliver the reports, and the w3wp.exe process peaked at 920mb of RAM usage. When we tried running three reports concurrently, RAM usage peaked around 1,220mb of RAM and Reporting Services usually timed out before delivering the reports.


Lastly, we put 2048mb of RAM in the server, and we could run three reports at once. It took around 06'58" to deliver the reports, and the w3wp.exe process peaked at 1,280mb of RAM usage. When we tried running four reports concurrently, RAM usage peaked around 1,290mb and Reporting Services died with a System.OutOfMemoryException exception.

The pattern we noticed with the first three steps, was that Reporting Services isn't using more than 80% of the physical RAM available on the server:

400mb / 512mb = roughly 80%

800mb / 1024mb = roughly 80%

1,220mb / 1536mb = roughly 80%

1,290mb / 2048mb = roughly 63%

Following the directions in "Configuring Available Memory for Reporting Services" (http://msdn2.microsoft.com/en-us/library/ms159206.aspx), we tried increasing the MemoryLimit of both the Web Service and the Windows Service.

We changed the memoryLimit in machine.config to:

<processModel autoConfig="true" memoryLimit="120" />

and the memoryLimit in rsreportserver.config to:

<MemoryLimit>120</MemoryLimit>
<MaximumMemoryLimit>140</MaximumMemoryLimit>

It looks like the ReportServer is reading this, as when it is restarting, the ReportServer_*.log file records:

w3wp!library!1!11/10/2006-10:20:56:: i INFO: Initializing MemoryLimit to '120' percent as specified in Configuration file.
w3wp!library!1!11/10/2006-10:20:56:: i INFO: Initializing MaximumMemoryLimit to '140' percent as specified in Configuration file.

However, it doesn't go above this 80% threshold.

More concerning is the System.OutOfMemoryException exception described above. Following the suggestions in KB 909678, we've tried tweaking the MemoryLimit and there is clearly still plenty of RAM left available on the server. This is happening repeatedly when we try to run four instances of the report concurrently.

Are there other parameters we should be adjusting to ensure Reporting Services uses the available RAM?

Thanks,
Andrew

Hi

We've made no progress on this issue.

Has anyone else found that setting the memoryLimit in the processModel element of machine.config seems to have no effect?

Thanks,
Andrew

|||

The memoryLimit setting in machine.config is deprecated if you are running IIS6. You should define worker process memory limits in IIS metabase, or use IIS manager UI.

I am curious about two things:

1. Is the other 20% of memory unused, or taken up by other processes?

2. Is there a timeout setting that can be tweaked?

|||

Sorry if that wouldn't be exactly on topic, but...

Having a report that is running for 2 1/2 hours is an issue by itself. Considering that report is based on a set of stored procedures, I would instead work on tweaking these stored procedures as well as settings on the SQL server that is providing you with such timing! Data volume returned by the SSRS is not an issue, report design is.

|||

We faced similar problems...

make sure that all the data area id's in the tables are connected to each other on an inner join (respective relationships maintained) ...

That will stop the memory leak ...and improve perfomance drastically

|||Are you suggesting that the queries that the report runs off of use inner joins? Or something else? Please give more detail.|||

Hi Glen

02'30" = two minutes and thirty seconds, not two hours and thirty minutes.

Thanks,
Andrew

Reporting Services CRM Feature Pack

I am trying to install the Reporting Services CRM Feature Pack on a SBS 2003.
I have installed Reporting Services and SP1 for it. Everything installed
just fine when I installed Reporting Services. I went to install the Feature
Pack for CRM (also installed on the same server) and at the very end of the
installation, I get the following error:
"Object reference not set to an instance of an object."
That is it and then it backs itself out of the installation. Any ideas? I
have not been successful researching the error as it is highly generic.
ThanksI posted a reply on www.MSCRMExperts.com.
Do you have SQL Reporting Services Service Pack 1 installed?
Y
"kleinmk" <kleinmk@.discussions.microsoft.com> wrote in message
news:B37047C3-42CD-4089-88AB-171A273B8AA6@.microsoft.com...
>I am trying to install the Reporting Services CRM Feature Pack on a SBS
>2003.
> I have installed Reporting Services and SP1 for it. Everything installed
> just fine when I installed Reporting Services. I went to install the
> Feature
> Pack for CRM (also installed on the same server) and at the very end of
> the
> installation, I get the following error:
> "Object reference not set to an instance of an object."
> That is it and then it backs itself out of the installation. Any ideas?
> I
> have not been successful researching the error as it is highly generic.
> Thanks|||Yes, Reporting Services SP1 is installed. This is actually a prereq. for it.
I do not think it will even let you begin to install if it detects that SP1
for RS is not there. I am seeing this same issue pop up quite a bit with
others having the same problem. No resolution on any of them though.
"Yoshi" wrote:
> I posted a reply on www.MSCRMExperts.com.
> Do you have SQL Reporting Services Service Pack 1 installed?
> Y
>
> "kleinmk" <kleinmk@.discussions.microsoft.com> wrote in message
> news:B37047C3-42CD-4089-88AB-171A273B8AA6@.microsoft.com...
> >I am trying to install the Reporting Services CRM Feature Pack on a SBS
> >2003.
> > I have installed Reporting Services and SP1 for it. Everything installed
> > just fine when I installed Reporting Services. I went to install the
> > Feature
> > Pack for CRM (also installed on the same server) and at the very end of
> > the
> > installation, I get the following error:
> >
> > "Object reference not set to an instance of an object."
> >
> > That is it and then it backs itself out of the installation. Any ideas?
> > I
> > have not been successful researching the error as it is highly generic.
> >
> > Thanks
>
>|||What version of the .Net Framework do you have installed?
Y
"kleinmk" <kleinmk@.discussions.microsoft.com> wrote in message
news:98C460C9-6792-43E1-A8F9-D03AC5C98DD5@.microsoft.com...
> Yes, Reporting Services SP1 is installed. This is actually a prereq. for
> it.
> I do not think it will even let you begin to install if it detects that
> SP1
> for RS is not there. I am seeing this same issue pop up quite a bit with
> others having the same problem. No resolution on any of them though.
> "Yoshi" wrote:
>> I posted a reply on www.MSCRMExperts.com.
>> Do you have SQL Reporting Services Service Pack 1 installed?
>> Y
>>
>> "kleinmk" <kleinmk@.discussions.microsoft.com> wrote in message
>> news:B37047C3-42CD-4089-88AB-171A273B8AA6@.microsoft.com...
>> >I am trying to install the Reporting Services CRM Feature Pack on a SBS
>> >2003.
>> > I have installed Reporting Services and SP1 for it. Everything
>> > installed
>> > just fine when I installed Reporting Services. I went to install the
>> > Feature
>> > Pack for CRM (also installed on the same server) and at the very end of
>> > the
>> > installation, I get the following error:
>> >
>> > "Object reference not set to an instance of an object."
>> >
>> > That is it and then it backs itself out of the installation. Any
>> > ideas?
>> > I
>> > have not been successful researching the error as it is highly generic.
>> >
>> > Thanks
>>|||This is on SBS 2003 (which is Windows Server 2003). By default, it comes
with .NET Framework v1.1.
"Yoshi" wrote:
> What version of the .Net Framework do you have installed?
> Y
>
> "kleinmk" <kleinmk@.discussions.microsoft.com> wrote in message
> news:98C460C9-6792-43E1-A8F9-D03AC5C98DD5@.microsoft.com...
> > Yes, Reporting Services SP1 is installed. This is actually a prereq. for
> > it.
> > I do not think it will even let you begin to install if it detects that
> > SP1
> > for RS is not there. I am seeing this same issue pop up quite a bit with
> > others having the same problem. No resolution on any of them though.
> >
> > "Yoshi" wrote:
> >
> >> I posted a reply on www.MSCRMExperts.com.
> >>
> >> Do you have SQL Reporting Services Service Pack 1 installed?
> >>
> >> Y
> >>
> >>
> >> "kleinmk" <kleinmk@.discussions.microsoft.com> wrote in message
> >> news:B37047C3-42CD-4089-88AB-171A273B8AA6@.microsoft.com...
> >> >I am trying to install the Reporting Services CRM Feature Pack on a SBS
> >> >2003.
> >> > I have installed Reporting Services and SP1 for it. Everything
> >> > installed
> >> > just fine when I installed Reporting Services. I went to install the
> >> > Feature
> >> > Pack for CRM (also installed on the same server) and at the very end of
> >> > the
> >> > installation, I get the following error:
> >> >
> >> > "Object reference not set to an instance of an object."
> >> >
> >> > That is it and then it backs itself out of the installation. Any
> >> > ideas?
> >> > I
> >> > have not been successful researching the error as it is highly generic.
> >> >
> >> > Thanks
> >>
> >>
> >>
>
>|||I am also getting this error:
"Object reference not set to an instance of an object."
I've verified that the ReportServer is running, however, I can't get the CRM
Report pack to install.|||Here is what we have found out. The environment we have is in Development
but mimics the production environment. So putting VS.NET on it (being SBS
2003 and mimicing the production environment) is not an option for this
circumstance (and is an optional install in the first place). Even though we
have unsuccessfully been able to install the CRM Report Pack, if you can
install it to another server NOT running the CRM, this seems to work. Then,
just publish your rdl from Reporting Services to the actual CRM Server
running Reporting Services. This assumes that you are running CRM and
Reporting Services on the same box.
"Chris" wrote:
> I am also getting this error:
> "Object reference not set to an instance of an object."
> I've verified that the ReportServer is running, however, I can't get the CRM
> Report pack to install.