Random long running queries

Random long running queries

Post by RG9uIFJvZ2 » Sat, 02 Apr 2005 04:09:03

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

Random long running queries

Post by Jacco Scha » Sat, 02 Apr 2005 20:29:22

When you look at the long running query, is it just the execution time that
is longer, or does it also do more reads or use more processor time? How
often does it exactly happen that the query takes longer than normal?

If it is just the execution time, it is most likely a factor external to the
query. Having auto-shrink on the database is one of the things that might
cause this. If it does more reads and/or uses more processing time, it can
be an indication that the statistics are out of date and a sub-optimal
execution plan is used. You can address this by updating the statistics on
that table on a regular basis.

Jacco Schalkwijk
SQL Server MVP


Random long running queries

Post by RG9uIFJvZ2 » Sun, 03 Apr 2005 02:19:02

It appears that there is little difference in the CPU time or reads between
a normal run of this query and a longer run. We noticed in our error log
that this was occuring once or twice a day over a month ago (other users were
receiving ODBC Timeouts trying to update records in the table). The
frequency started increasing three weeks ago and now it occurs multiple times
each hour during the times of peak activity of our users.

Yesterday it was suggested to turn off auto-shrink. I also updated the
statistics manually yesterday afternoon. Peak user time is beginning about
now, so I have my fingers crossed.

"Jacco Schalkwijk" wrote: