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.