Query Hangs/Time out

I have an intermittent problem that I have not been able to fix.
I have a database that will all of a sudden start timing out on a particular
query that uses both stored procedures and Views on an aspx page. If I
perform the same query in Query Analyzer it will just hang...I've let it go
10 minutes and no result.
I looked for blocked processes when this occurs - there are none. I've
checked the error log, nothing.
To correct this problem, I've found that if I simply copy the database to a
devel box the query on the prod server will again work fine - then we can go
several hours to several days before it starts hanging again.
Now, if I try to put the prod database in single user, then detach, then
re-attach, the query still hangs.
I'm assuming a reboot of the server itself would do the trick too...but
avoiding that for obvious reasons.
We have not run into this on any of our other databases on either the
production server or our development desktops. We have not been able to
recreate this condition on a test system.
The devel/test boxes are xp pro..latest service paks.
The production server is win2003..latest service paks too.
We are on SQL2000 SP4 on all systems.
Has anyone heard of this? Any help would be appreciated.Reid,
I would suggest tunning the query(s). Generate a query plan and concentrate
on the steps that take up most of the % of the execution plan. Add indexes
where identified typically columns used in the where clause are a good place
to start and see if that helps. If there are indexes already on the table(s)
and or view(s) then I would suggest a DBCC reindex to reindex the indexes so
they will preform better. I had the exact same issue with the aspx page that
went into production and ran fine for 3 months and they hung. I added an
index or two and it started running in less than a 1 second so indexes are a
wonderful thing.
Hope this helps,
Rick Butler MCP
"Reid" wrote:
> I have an intermittent problem that I have not been able to fix.
> I have a database that will all of a sudden start timing out on a particular
> query that uses both stored procedures and Views on an aspx page. If I
> perform the same query in Query Analyzer it will just hang...I've let it go
> 10 minutes and no result.
> I looked for blocked processes when this occurs - there are none. I've
> checked the error log, nothing.
> To correct this problem, I've found that if I simply copy the database to a
> devel box the query on the prod server will again work fine - then we can go
> several hours to several days before it starts hanging again.
> Now, if I try to put the prod database in single user, then detach, then
> re-attach, the query still hangs.
> I'm assuming a reboot of the server itself would do the trick too...but
> avoiding that for obvious reasons.
> We have not run into this on any of our other databases on either the
> production server or our development desktops. We have not been able to
> recreate this condition on a test system.
> The devel/test boxes are xp pro..latest service paks.
> The production server is win2003..latest service paks too.
> We are on SQL2000 SP4 on all systems.
> Has anyone heard of this? Any help would be appreciated.|||RcsterMan,
Thanks for the suggestions. I've actually done all of that...they where
the first things I tried. This situation is intermittent...if I was having
tuning issues would I not be having more consistant hangs? After I do the DB
copy, or reboot the server, the site runs fine and runs the querys in several
seconds - which is quite acceptable due to the complexity of the query. But
then it simply hangs - the performance doesn't degrade, it simply hangs -
While I can do other searches/query's with no issues.
I knew this would be a very nebulas problem to bug out..any other suggestions?
Thanks in advance for your time,
Reid
"RicsterMan" wrote:
> Reid,
> I would suggest tunning the query(s). Generate a query plan and concentrate
> on the steps that take up most of the % of the execution plan. Add indexes
> where identified typically columns used in the where clause are a good place
> to start and see if that helps. If there are indexes already on the table(s)
> and or view(s) then I would suggest a DBCC reindex to reindex the indexes so
> they will preform better. I had the exact same issue with the aspx page that
> went into production and ran fine for 3 months and they hung. I added an
> index or two and it started running in less than a 1 second so indexes are a
> wonderful thing.
> Hope this helps,
> Rick Butler MCP
> "Reid" wrote:
> > I have an intermittent problem that I have not been able to fix.
> >
> > I have a database that will all of a sudden start timing out on a particular
> > query that uses both stored procedures and Views on an aspx page. If I
> > perform the same query in Query Analyzer it will just hang...I've let it go
> > 10 minutes and no result.
> >
> > I looked for blocked processes when this occurs - there are none. I've
> > checked the error log, nothing.
> >
> > To correct this problem, I've found that if I simply copy the database to a
> > devel box the query on the prod server will again work fine - then we can go
> > several hours to several days before it starts hanging again.
> >
> > Now, if I try to put the prod database in single user, then detach, then
> > re-attach, the query still hangs.
> >
> > I'm assuming a reboot of the server itself would do the trick too...but
> > avoiding that for obvious reasons.
> >
> > We have not run into this on any of our other databases on either the
> > production server or our development desktops. We have not been able to
> > recreate this condition on a test system.
> >
> > The devel/test boxes are xp pro..latest service paks.
> >
> > The production server is win2003..latest service paks too.
> >
> > We are on SQL2000 SP4 on all systems.
> >
> > Has anyone heard of this? Any help would be appreciated.|||What is the wait type when the query hangs? Is it doing
anything or just actually just sitting there - did you check
CPU, Memory, IO for the SPID while the query appears to
hang?
What's going on with the locks when this happens? Have you
run profiler to see what else is going on? Did you also
monitor for recompiles, statistics being updated? Did you
check for issues with file size changes during this?
Anything in particular going on with system itself at the
time the process seems to hang?
-Sue
On Mon, 16 Jan 2006 20:56:03 -0800, "Reid"
<Reid@.discussions.microsoft.com> wrote:
>RcsterMan,
>Thanks for the suggestions. I've actually done all of that...they where
>the first things I tried. This situation is intermittent...if I was having
>tuning issues would I not be having more consistant hangs? After I do the DB
>copy, or reboot the server, the site runs fine and runs the querys in several
>seconds - which is quite acceptable due to the complexity of the query. But
>then it simply hangs - the performance doesn't degrade, it simply hangs -
>While I can do other searches/query's with no issues.
>I knew this would be a very nebulas problem to bug out..any other suggestions?
>Thanks in advance for your time,
>Reid
>"RicsterMan" wrote:
>> Reid,
>> I would suggest tunning the query(s). Generate a query plan and concentrate
>> on the steps that take up most of the % of the execution plan. Add indexes
>> where identified typically columns used in the where clause are a good place
>> to start and see if that helps. If there are indexes already on the table(s)
>> and or view(s) then I would suggest a DBCC reindex to reindex the indexes so
>> they will preform better. I had the exact same issue with the aspx page that
>> went into production and ran fine for 3 months and they hung. I added an
>> index or two and it started running in less than a 1 second so indexes are a
>> wonderful thing.
>> Hope this helps,
>> Rick Butler MCP
>> "Reid" wrote:
>> > I have an intermittent problem that I have not been able to fix.
>> >
>> > I have a database that will all of a sudden start timing out on a particular
>> > query that uses both stored procedures and Views on an aspx page. If I
>> > perform the same query in Query Analyzer it will just hang...I've let it go
>> > 10 minutes and no result.
>> >
>> > I looked for blocked processes when this occurs - there are none. I've
>> > checked the error log, nothing.
>> >
>> > To correct this problem, I've found that if I simply copy the database to a
>> > devel box the query on the prod server will again work fine - then we can go
>> > several hours to several days before it starts hanging again.
>> >
>> > Now, if I try to put the prod database in single user, then detach, then
>> > re-attach, the query still hangs.
>> >
>> > I'm assuming a reboot of the server itself would do the trick too...but
>> > avoiding that for obvious reasons.
>> >
>> > We have not run into this on any of our other databases on either the
>> > production server or our development desktops. We have not been able to
>> > recreate this condition on a test system.
>> >
>> > The devel/test boxes are xp pro..latest service paks.
>> >
>> > The production server is win2003..latest service paks too.
>> >
>> > We are on SQL2000 SP4 on all systems.
>> >
>> > Has anyone heard of this? Any help would be appreciated.|||Sue,
I'll need to wait until it starts doing it again. Right now it is working
as it is supposed to. I'll pay attention to the info on the SPID as
suggested.
I have run profiler. In fact, I believe at one time when the query was
hanging I ran the Index tuning wizard and it came back with some suggestions
that I accepted. Tried to re-run the query and it continued to hang. So I
did the copy db again and tried the query and it worked fine.
The rest of the server appears to work fine when this is occuring. I can do
other queries/operations on the site and no problems. It seems to be
isolated to this one db.
I'll post when I have more info..thanks for you time.
"Sue Hoegemeier" wrote:
> What is the wait type when the query hangs? Is it doing
> anything or just actually just sitting there - did you check
> CPU, Memory, IO for the SPID while the query appears to
> hang?
> What's going on with the locks when this happens? Have you
> run profiler to see what else is going on? Did you also
> monitor for recompiles, statistics being updated? Did you
> check for issues with file size changes during this?
> Anything in particular going on with system itself at the
> time the process seems to hang?
> -Sue
> On Mon, 16 Jan 2006 20:56:03 -0800, "Reid"
> <Reid@.discussions.microsoft.com> wrote:
> >RcsterMan,
> >
> >Thanks for the suggestions. I've actually done all of that...they where
> >the first things I tried. This situation is intermittent...if I was having
> >tuning issues would I not be having more consistant hangs? After I do the DB
> >copy, or reboot the server, the site runs fine and runs the querys in several
> >seconds - which is quite acceptable due to the complexity of the query. But
> >then it simply hangs - the performance doesn't degrade, it simply hangs -
> >While I can do other searches/query's with no issues.
> >
> >I knew this would be a very nebulas problem to bug out..any other suggestions?
> >
> >Thanks in advance for your time,
> >Reid
> >
> >"RicsterMan" wrote:
> >
> >> Reid,
> >> I would suggest tunning the query(s). Generate a query plan and concentrate
> >> on the steps that take up most of the % of the execution plan. Add indexes
> >> where identified typically columns used in the where clause are a good place
> >> to start and see if that helps. If there are indexes already on the table(s)
> >> and or view(s) then I would suggest a DBCC reindex to reindex the indexes so
> >> they will preform better. I had the exact same issue with the aspx page that
> >> went into production and ran fine for 3 months and they hung. I added an
> >> index or two and it started running in less than a 1 second so indexes are a
> >> wonderful thing.
> >>
> >> Hope this helps,
> >> Rick Butler MCP
> >>
> >> "Reid" wrote:
> >>
> >> > I have an intermittent problem that I have not been able to fix.
> >> >
> >> > I have a database that will all of a sudden start timing out on a particular
> >> > query that uses both stored procedures and Views on an aspx page. If I
> >> > perform the same query in Query Analyzer it will just hang...I've let it go
> >> > 10 minutes and no result.
> >> >
> >> > I looked for blocked processes when this occurs - there are none. I've
> >> > checked the error log, nothing.
> >> >
> >> > To correct this problem, I've found that if I simply copy the database to a
> >> > devel box the query on the prod server will again work fine - then we can go
> >> > several hours to several days before it starts hanging again.
> >> >
> >> > Now, if I try to put the prod database in single user, then detach, then
> >> > re-attach, the query still hangs.
> >> >
> >> > I'm assuming a reboot of the server itself would do the trick too...but
> >> > avoiding that for obvious reasons.
> >> >
> >> > We have not run into this on any of our other databases on either the
> >> > production server or our development desktops. We have not been able to
> >> > recreate this condition on a test system.
> >> >
> >> > The devel/test boxes are xp pro..latest service paks.
> >> >
> >> > The production server is win2003..latest service paks too.
> >> >
> >> > We are on SQL2000 SP4 on all systems.
> >> >
> >> > Has anyone heard of this? Any help would be appreciated.
>