Reporting Services Report Hangs

--
Thanks, JimHit enter too soon. Anyway, I have a report against a database that has full
statistics recalculated every night. The report basically returns all
records in the database for a specified date range. If I run with a range of
10 minutes I get 715 rows in under a minute. During this time w3wp.exe pops
CPU rate up to about 68-70% briefly and then back down to normal <20%. Soon
after that, few seconds, the data is displayed in report manager. If I run
report for 60 minute time span (which would be 7000-7500 rows), the w3wp.exe
behaves the same but the data never (at least waiting an hour before I stop
it) displays in report manager. All during this waiting time frame the CPU
is <20%. Database has indexes and as mentioned above full statistics are
calculated every night as part of a maintenance plan. This one's got me
baffled. Appreciate any insight.
--
Thanks, Jim
"Jim B" wrote:
> --
> Thanks, Jim|||Version? What type of rendering are you doing? During an export operation
(for instance to PDF or Excel). Size of report, etc.
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Jim B" <JB@.lightning.com> wrote in message
news:B0A11094-CEC6-4A68-BB52-FF7028BC782E@.microsoft.com...
> --
> Thanks, Jim|||Hmmm, number of rows should not be an issue. No problem with that amount of
data. Have you tried running the query and getting the query plan. Perhaps
something is happening where it ends up doing table scans.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Jim B" <JB@.lightning.com> wrote in message
news:D75A740C-E969-4416-A165-8CB473F0549A@.microsoft.com...
> Hit enter too soon. Anyway, I have a report against a database that has
> full
> statistics recalculated every night. The report basically returns all
> records in the database for a specified date range. If I run with a range
> of
> 10 minutes I get 715 rows in under a minute. During this time w3wp.exe
> pops
> CPU rate up to about 68-70% briefly and then back down to normal <20%.
> Soon
> after that, few seconds, the data is displayed in report manager. If I
> run
> report for 60 minute time span (which would be 7000-7500 rows), the
> w3wp.exe
> behaves the same but the data never (at least waiting an hour before I
> stop
> it) displays in report manager. All during this waiting time frame the
> CPU
> is <20%. Database has indexes and as mentioned above full statistics are
> calculated every night as part of a maintenance plan. This one's got me
> baffled. Appreciate any insight.
> --
> Thanks, Jim
>
> "Jim B" wrote:
>> --
>> Thanks, Jim|||Bruce;
Thanks for the reply. I'm just doing a standard HTML rendering. I haven't
examined the query plan yet but I'll do that. I have been running some more
trials with the following observation. I first sign in to site (I'm doing
access via virtual machine and VPN clients) and start up an IE browser on
that server's desktop. I bring up reporting manager fine and choose my
report. I enter the date range and hit view report. In a reasonable time
the report comes up (very soon after green circle quits rotating which is
typical. I can do this again (different date range ) about 3-5 times before
the report hangs. After clicking on view report of course the w3wp.exe shows
a peak in CPU activity in task manager and green circle is rotating. After
the w3wp.exe CPU activity falls off the green circle quits rotating and
nothing further happens, i.e. the data never shows up. This is what I mean
by hanging. I've checked the RS logs and there is nothing new in them since
the initial starting of Reporting Manager. Once the issue with data not
showing occurs no other reports will successfully display either although
w3wp.exe would indicate that they are being processed. While still logged in
to customer's site, I discovered that if I close the browser completely (IE6)
and then start a new browser session this behavior disappears. That is, I
can run report after report without any hanging. However, if I log off site
and log back in the same pattern repeats itself and can be resolved by
closing and restarting the browser. Of course my customers (report users)
won't stand for the having to close browser and try their reports again. I
did verify that RS is running in it's own application pool within IIS.
--
Thanks, Jim
"Bruce L-C [MVP]" wrote:
> Version? What type of rendering are you doing? During an export operation
> (for instance to PDF or Excel). Size of report, etc.
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Jim B" <JB@.lightning.com> wrote in message
> news:B0A11094-CEC6-4A68-BB52-FF7028BC782E@.microsoft.com...
> >
> > --
> > Thanks, Jim
>
>|||Forgot to mention that this is SQL2005 reporting services running on server
with 2003OS.
--
Thanks, Jim
"Jim B" wrote:
> Bruce;
> Thanks for the reply. I'm just doing a standard HTML rendering. I haven't
> examined the query plan yet but I'll do that. I have been running some more
> trials with the following observation. I first sign in to site (I'm doing
> access via virtual machine and VPN clients) and start up an IE browser on
> that server's desktop. I bring up reporting manager fine and choose my
> report. I enter the date range and hit view report. In a reasonable time
> the report comes up (very soon after green circle quits rotating which is
> typical. I can do this again (different date range ) about 3-5 times before
> the report hangs. After clicking on view report of course the w3wp.exe shows
> a peak in CPU activity in task manager and green circle is rotating. After
> the w3wp.exe CPU activity falls off the green circle quits rotating and
> nothing further happens, i.e. the data never shows up. This is what I mean
> by hanging. I've checked the RS logs and there is nothing new in them since
> the initial starting of Reporting Manager. Once the issue with data not
> showing occurs no other reports will successfully display either although
> w3wp.exe would indicate that they are being processed. While still logged in
> to customer's site, I discovered that if I close the browser completely (IE6)
> and then start a new browser session this behavior disappears. That is, I
> can run report after report without any hanging. However, if I log off site
> and log back in the same pattern repeats itself and can be resolved by
> closing and restarting the browser. Of course my customers (report users)
> won't stand for the having to close browser and try their reports again. I
> did verify that RS is running in it's own application pool within IIS.
> --
> Thanks, Jim
>
> "Bruce L-C [MVP]" wrote:
> > Version? What type of rendering are you doing? During an export operation
> > (for instance to PDF or Excel). Size of report, etc.
> >
> > --
> > Bruce Loehle-Conger
> > MVP SQL Server Reporting Services
> >
> > "Jim B" <JB@.lightning.com> wrote in message
> > news:B0A11094-CEC6-4A68-BB52-FF7028BC782E@.microsoft.com...
> > >
> > > --
> > > Thanks, Jim
> >
> >
> >|||I think you might be seeing an artifact due to how you are configured for
your testing and that the users will not see this. I used to have some
issues with RS 2000 but never with RS 2005. Especially since you are using
HTML I am confident that something is going on with all the hoops you are
going through to test (which your users won't be doing). I have been live
with RS 2005 for a long time and neither myself or my users ever have an
issue and never have to close the browser. We too are on IE6. I don't have a
lot of people but some are very intensive users.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Jim B" <JB@.lightning.com> wrote in message
news:C977F613-7BCC-4317-9B7F-049E45F16635@.microsoft.com...
> Bruce;
> Thanks for the reply. I'm just doing a standard HTML rendering. I
> haven't
> examined the query plan yet but I'll do that. I have been running some
> more
> trials with the following observation. I first sign in to site (I'm doing
> access via virtual machine and VPN clients) and start up an IE browser on
> that server's desktop. I bring up reporting manager fine and choose my
> report. I enter the date range and hit view report. In a reasonable time
> the report comes up (very soon after green circle quits rotating which is
> typical. I can do this again (different date range ) about 3-5 times
> before
> the report hangs. After clicking on view report of course the w3wp.exe
> shows
> a peak in CPU activity in task manager and green circle is rotating.
> After
> the w3wp.exe CPU activity falls off the green circle quits rotating and
> nothing further happens, i.e. the data never shows up. This is what I
> mean
> by hanging. I've checked the RS logs and there is nothing new in them
> since
> the initial starting of Reporting Manager. Once the issue with data not
> showing occurs no other reports will successfully display either although
> w3wp.exe would indicate that they are being processed. While still logged
> in
> to customer's site, I discovered that if I close the browser completely
> (IE6)
> and then start a new browser session this behavior disappears. That is, I
> can run report after report without any hanging. However, if I log off
> site
> and log back in the same pattern repeats itself and can be resolved by
> closing and restarting the browser. Of course my customers (report users)
> won't stand for the having to close browser and try their reports again.
> I
> did verify that RS is running in it's own application pool within IIS.
> --
> Thanks, Jim
>
> "Bruce L-C [MVP]" wrote:
>> Version? What type of rendering are you doing? During an export operation
>> (for instance to PDF or Excel). Size of report, etc.
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> "Jim B" <JB@.lightning.com> wrote in message
>> news:B0A11094-CEC6-4A68-BB52-FF7028BC782E@.microsoft.com...
>> >
>> > --
>> > Thanks, Jim
>>|||Hi Jim
Have you tired running your query in sql server. Say your report uses a
stored procedure sp_whatever. Run this sproc for the time period you want to
see if it actually returns a record. The report may be hunging because the
record return ( the 7000 to 7500 rows) are not returned.
Also go to your DB server and check the connection time out. If your time
out is too soon, the report may be experiencing a TCP connection out causing
it to wait...
Thanks
"Bruce L-C [MVP]" wrote:
> I think you might be seeing an artifact due to how you are configured for
> your testing and that the users will not see this. I used to have some
> issues with RS 2000 but never with RS 2005. Especially since you are using
> HTML I am confident that something is going on with all the hoops you are
> going through to test (which your users won't be doing). I have been live
> with RS 2005 for a long time and neither myself or my users ever have an
> issue and never have to close the browser. We too are on IE6. I don't have a
> lot of people but some are very intensive users.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Jim B" <JB@.lightning.com> wrote in message
> news:C977F613-7BCC-4317-9B7F-049E45F16635@.microsoft.com...
> > Bruce;
> > Thanks for the reply. I'm just doing a standard HTML rendering. I
> > haven't
> > examined the query plan yet but I'll do that. I have been running some
> > more
> > trials with the following observation. I first sign in to site (I'm doing
> > access via virtual machine and VPN clients) and start up an IE browser on
> > that server's desktop. I bring up reporting manager fine and choose my
> > report. I enter the date range and hit view report. In a reasonable time
> > the report comes up (very soon after green circle quits rotating which is
> > typical. I can do this again (different date range ) about 3-5 times
> > before
> > the report hangs. After clicking on view report of course the w3wp.exe
> > shows
> > a peak in CPU activity in task manager and green circle is rotating.
> > After
> > the w3wp.exe CPU activity falls off the green circle quits rotating and
> > nothing further happens, i.e. the data never shows up. This is what I
> > mean
> > by hanging. I've checked the RS logs and there is nothing new in them
> > since
> > the initial starting of Reporting Manager. Once the issue with data not
> > showing occurs no other reports will successfully display either although
> > w3wp.exe would indicate that they are being processed. While still logged
> > in
> > to customer's site, I discovered that if I close the browser completely
> > (IE6)
> > and then start a new browser session this behavior disappears. That is, I
> > can run report after report without any hanging. However, if I log off
> > site
> > and log back in the same pattern repeats itself and can be resolved by
> > closing and restarting the browser. Of course my customers (report users)
> > won't stand for the having to close browser and try their reports again.
> > I
> > did verify that RS is running in it's own application pool within IIS.
> > --
> > Thanks, Jim
> >
> >
> > "Bruce L-C [MVP]" wrote:
> >
> >> Version? What type of rendering are you doing? During an export operation
> >> (for instance to PDF or Excel). Size of report, etc.
> >>
> >> --
> >> Bruce Loehle-Conger
> >> MVP SQL Server Reporting Services
> >>
> >> "Jim B" <JB@.lightning.com> wrote in message
> >> news:B0A11094-CEC6-4A68-BB52-FF7028BC782E@.microsoft.com...
> >> >
> >> > --
> >> > Thanks, Jim
> >>
> >>
> >>
>
>