Re: Random long running queries
- From: "Don Rogers" <DonRogers@xxxxxxxxxxxxxxxxxxxxxxxxx>
- Date: Fri, 1 Apr 2005 09:19:02 -0800
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@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
> news:FEE04A48-59F8-4B51-80B8-05FE06D2775A@xxxxxxxxxxxxxxxx
> >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
>
>
>
.
- References:
- Random long running queries
- From: Don Rogers
- Re: Random long running queries
- From: Jacco Schalkwijk
- Random long running queries
- Prev by Date: Connection.Execute and SQL stored procedures
- Next by Date: Re: Timeout
- Previous by thread: Re: Random long running queries
- Next by thread: URGENT - Exception_Access_Violation
- Index(es):
Relevant Pages
|
