MS SQL Server ODBC - Random long running queries

Author

Random long running queries

Don Rogers

2005-03-31, 8:01 pm

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

Jacco Schalkwijk

2005-04-01, 7:01 am

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

"Don Rogers" < DonRogers@discussion
s.microsoft.com> wrote in message
news:FEE04A48-59F8-4B51-80B8- 05FE06D2775A@microso
ft.com...
>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

Don Rogers

2005-04-01, 8:01 pm

Jacco,
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.
Thanks,

"Jacco Schalkwijk" wrote:

> 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
>
>
> "Don Rogers" < DonRogers@discussion
s.microsoft.com> wrote in message
> news:FEE04A48-59F8-4B51-80B8- 05FE06D2775A@microso
ft.com...
>
>
>

Sponsored Links