queries hanging?

I am running SQL2000, occasionally queries take a huge amount of time to
process, for some reason they seem to pause for a couple of minutes. For
example, the following query normally executes in a couple of seconds...
SELECT count(*)
FROM object ob
JOIN quantity qt ON ob.objid=qt.objid
JOIN site st ON qt.siteid=st.siteid
JOIN cussite cs ON cs.siteid=st.siteid
WHERE cs.cusid=4900 AND ob.link=1 AND qt.lupd>'1/1/1900'
but for some reason, today it is taking over 10 minutes!!
Running sp_lock2 reveals..
TABLE LOCKTYPE MODE
OBJECT TAB Sch-S
QUANTITY TAB Sch-S
SITE TAB Sch-S
CUSSITE TAB Sch-S
The query just sits there with those locks for over 10 minutes before coming
back with the data.
Any help much appreciated...
MarkMark Baldwin wrote:
> I am running SQL2000, occasionally queries take a huge amount of time to
> process, for some reason they seem to pause for a couple of minutes. For
> example, the following query normally executes in a couple of seconds...
> SELECT count(*)
> FROM object ob
> JOIN quantity qt ON ob.objid=qt.objid
> JOIN site st ON qt.siteid=st.siteid
> JOIN cussite cs ON cs.siteid=st.siteid
> WHERE cs.cusid=4900 AND ob.link=1 AND qt.lupd>'1/1/1900'
> but for some reason, today it is taking over 10 minutes!!
> Running sp_lock2 reveals..
> TABLE LOCKTYPE MODE
> OBJECT TAB Sch-S
> QUANTITY TAB Sch-S
> SITE TAB Sch-S
> CUSSITE TAB Sch-S
> The query just sits there with those locks for over 10 minutes before coming
> back with the data.
> Any help much appreciated...
> Mark
>
Review the execution plan, make sure the proper indexes are there to
support the query, check for blocking, all the typical things...
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||I wish I could review the execution plan! In query analyser, selecting
"display estimated execution plan" with noexec or simply ticking the
"display estimated execution plan" option results in a long pause (10minutes
or more) before coming back with an empty execution plan and no error.
This is the same pause that affects my queries. So it's not the query thats
taking the time, its the pre processing of the query.
--
Best regards
Mark
"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:45B63900.5090009@.realsqlguy.com...
> Mark Baldwin wrote:
>> I am running SQL2000, occasionally queries take a huge amount of time to
>> process, for some reason they seem to pause for a couple of minutes. For
>> example, the following query normally executes in a couple of seconds...
>> SELECT count(*)
>> FROM object ob
>> JOIN quantity qt ON ob.objid=qt.objid
>> JOIN site st ON qt.siteid=st.siteid
>> JOIN cussite cs ON cs.siteid=st.siteid
>> WHERE cs.cusid=4900 AND ob.link=1 AND qt.lupd>'1/1/1900'
>> but for some reason, today it is taking over 10 minutes!!
>> Running sp_lock2 reveals..
>> TABLE LOCKTYPE MODE
>> OBJECT TAB Sch-S
>> QUANTITY TAB Sch-S
>> SITE TAB Sch-S
>> CUSSITE TAB Sch-S
>> The query just sits there with those locks for over 10 minutes before
>> coming back with the data.
>> Any help much appreciated...
>> Mark
> Review the execution plan, make sure the proper indexes are there to
> support the query, check for blocking, all the typical things...
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com|||Mark Baldwin wrote:
> I wish I could review the execution plan! In query analyser, selecting
> "display estimated execution plan" with noexec or simply ticking the
> "display estimated execution plan" option results in a long pause (10minutes
> or more) before coming back with an empty execution plan and no error.
> This is the same pause that affects my queries. So it's not the query thats
> taking the time, its the pre processing of the query.
>
Are your statistics current? Maybe something here will help:
http://groups.google.com/group/microsoft.public.sqlserver.server/browse_frm/thread/90a81b1ebd25891d/c7eea6596a0b6011
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Look at your Task Manager on the *client*. Is something taking up a lot
of CPU on it? What about the server?
-Dave
Mark Baldwin wrote:
> I wish I could review the execution plan! In query analyser, selecting
> "display estimated execution plan" with noexec or simply ticking the
> "display estimated execution plan" option results in a long pause (10minutes
> or more) before coming back with an empty execution plan and no error.
> This is the same pause that affects my queries. So it's not the query thats
> taking the time, its the pre processing of the query.
>
-Dave Markle
http://www.markleconsulting.com/blogsql