Reporting Services - Overload and OutofMemoryException - Workaround?

We're having a good bit of trouble with a deployed report overloading
the server during times of heavy use. The report renders directly
from an external app to Excel, running the report by way of URL. I
haven't found any tuning that helps us - we have about 1200 clients
who try to run the report in a 2-hour span. Sometimes it seems like
they're all hitting it in the same 10 minutes. I can see the activity
in the performance monitor. Once active report requests reach 40 or
so, we usually get an OutOfMemory exception and RS recycles. It picks
back up, but the users who were waiting all get errors.
I couldn't find a metering option to force requests into a 'holding'
queue if active requests reach a threshold, so I'm trying to build a
workaround. I'm posting the basics of it here, in case it might help
anyone else.
' This code will go into the page that redirects users
' to the report. The page will either prompt the user
' to refresh or will have a refresh META tag
Dim rs As New sqlrpts.ReportingService
rs.Credentials = New System.Net.NetworkCredential _
("userid", "password", "domain")
' or - System.Net.CredentialCache.DefaultCredentials()
Dim jobs As sqlrpts.Job
Dim intx As Int16 = 0
For Each jobs In rs.ListJobs
intx = intx + 1
Next
' If intx is above the threshold we'll set (probably 25-30),
' we'll display a 'please wait' message. Otherwise, we'll
' redirect to the report URL
If anyone knows a better way around this, please reply on this thread
or email me.
Thanks!
Tim ShayHi Tim,
Just need to check with you quickly, which counter are you using to
determine the OutOfMemory exception and RS recycle?
Also, have you tried using ACT to test the report URL and see how many
RPS it can process? Coz we're having similar problem and is wondering
why RS cannot process concurrent user requests for the web application
we're developing.
Appreciate your input, thanks!
CCC
tshay@.foodlion.com (TShay) wrote in message news:<1fe4fd2c.0409100828.795a3ae3@.posting.google.com>...
> We're having a good bit of trouble with a deployed report overloading
> the server during times of heavy use. The report renders directly
> from an external app to Excel, running the report by way of URL. I
> haven't found any tuning that helps us - we have about 1200 clients
> who try to run the report in a 2-hour span. Sometimes it seems like
> they're all hitting it in the same 10 minutes. I can see the activity
> in the performance monitor. Once active report requests reach 40 or
> so, we usually get an OutOfMemory exception and RS recycles. It picks
> back up, but the users who were waiting all get errors.
> I couldn't find a metering option to force requests into a 'holding'
> queue if active requests reach a threshold, so I'm trying to build a
> workaround. I'm posting the basics of it here, in case it might help
> anyone else.
> ' This code will go into the page that redirects users
> ' to the report. The page will either prompt the user
> ' to refresh or will have a refresh META tag
> Dim rs As New sqlrpts.ReportingService
> rs.Credentials = New System.Net.NetworkCredential _
> ("userid", "password", "domain")
> ' or - System.Net.CredentialCache.DefaultCredentials()
> Dim jobs As sqlrpts.Job
> Dim intx As Int16 = 0
> For Each jobs In rs.ListJobs
> intx = intx + 1
> Next
> ' If intx is above the threshold we'll set (probably 25-30),
> ' we'll display a 'please wait' message. Otherwise, we'll
> ' redirect to the report URL
> If anyone knows a better way around this, please reply on this thread
> or email me.
> Thanks!
> Tim Shay