Random long running queries

I have an Access XP application running on user's desktops. This application
is linked to a SQL Server 2000 DB. For three weeks now we have been noticing
an increasing number of ODBC timeout errors. Tracking down the cause of the
errors has led us to run Profiler traces, SP_Who2 and SP_Lock. We have also
been monitoring the logs.

This what I have found:

I can now see that the problem begins with a query (not always the same one)
against the primary table in the DB (but only 450k records). The queries
have been modified several time in the past week to bring back fewer and
fewer records, so that now typically there are only 50 - 100 records
returned. In fact two days ago we changed one of the queries to a stored
procedure. All of these queries normally complete running in under 2 seconds
(over 95% of the time). However, even the stored procedure can take up to 5
minutes to complete at random times. This causes blocking of other processes
that require exclusive locks on specific records for updating the data.
These blocked processes eventually time out (thus the Timeout error).
However the stored procedure (or query keep running till the bitter end).

Then, suddenly as if the sun came out the same query starts performing in
it's typical breezy fashion and all the users are happy again (until the next
4 minute query).

I can run the same query repeatedly (that is the stored procedure with the
same parameters), and not recreate the problem. I have however, started up a
test when a blocking incident had already started (which makes it easy for me
to know when to run SP_Who2).

Any ideas as to what can cause SQL Server to suddenly act like it is frozen
up? There is another DB on the same Server which is not experiencing any of
the same problems. The system and SQL Server logs show nothing unusual.

Thank you,
--
Don Rogers
.

Relevant Pages

  • Re: Index Seek (or) Index Scan in Execution Plan
    ... your queries do run better when you use them. ... Run your query with and without the hint, ... SQL Server MVP ... >>>> It is preferable to not use index hints, ...
    (microsoft.public.sqlserver.server)
  • Re: Index Seek (or) Index Scan in Execution Plan
    ... your queries do run better when you use them. ... Run your query with and without the hint, ... SQL Server MVP ... >>>> It is preferable to not use index hints, ...
    (microsoft.public.sqlserver.programming)
  • Re: slowing/halting stored procedure from ado.net
    ... calling the same stored procedure from SQL> Management ... > Studio goes without any slowdown, ... > There was/is no big job running on the sql server, ... >> Erratic performance of any query is usually due to something changing. ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: Ongoing purging of active records causes deadlocks
    ... For a query like: ... plan for some of our queries and it looks like it sorts first, ... What is a possible alternative, though, is that the purge first performs: ... Books Online for SQL Server 2005 athttp://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books ... ...
    (comp.databases.ms-sqlserver)
  • Re: Database Engine Tuning Advisor suggestion to replace syntax.
    ... Actually, what I said was "IF this code is running in a stored procedure", ... execution with the execution plan generated for the first execution. ... SQL Server does not have to compile the second statement." ... replace query 1 below with the syntax in query 2 below. ...
    (microsoft.public.sqlserver.tools)
Loading