Performance Problem

I am experience a very strange performance problem. A nightly job that had
been consistently running 2 hours each night is suddenly running 16 hours.
A trace reveals a section of code taking 2500-3800 milliseconds of CPU to
process. The execution plan for the select statement shows the indexes are
correctly being selected and an index s is being performed on both tables
in the select. There are approx 400,000 rows, and the trace says it is
reading every row (although index sing)
If I run the same select statement in query analyzer while the Agent job is
running it processes in about 35 milliseconds, reading less than 60 rows (As
it should)
Any ideas on why I am getting such a large performance gap'
help!
Thanks
Richard DouglassIt's probably something that changed between then and now.
"Richard Douglass" wrote:

> I am experience a very strange performance problem. A nightly job that ha
d
> been consistently running 2 hours each night is suddenly running 16 hours.
> A trace reveals a section of code taking 2500-3800 milliseconds of CPU to
> process. The execution plan for the select statement shows the indexes ar
e
> correctly being selected and an index s is being performed on both tabl
es
> in the select. There are approx 400,000 rows, and the trace says it is
> reading every row (although index sing)
> If I run the same select statement in query analyzer while the Agent job i
s
> running it processes in about 35 milliseconds, reading less than 60 rows (
As
> it should)
> Any ideas on why I am getting such a large performance gap'
> help!
> Thanks
> Richard Douglass
>
>|||Nothing has changed. The code is stable and has not been modified in almost
a year.
It makes no sense that the SELECT in the job runs for 3000 milliseconds and
a cut-n-paste of the same query runs in 40 milliseconds in query analyzer.
Both the job and QA produce the exact same execution plan (Same logical
look, both tables index sing)
"KH" <KH@.discussions.microsoft.com> wrote in message
news:7DEC1462-2C11-4340-BAA2-8DD92B47C0C5@.microsoft.com...
> It's probably something that changed between then and now.
>
> "Richard Douglass" wrote:
>
had
hours.
to
are
tables
is
(As|||Is there anything to the timing that your "nightly" job runs at night
(during backups?) and your QueryAnalyzer job you test is run during the day?
(grasping at straws?)
Message posted via http://www.webservertalk.com|||Richard, I saw a very similiar thing at one of my customers.. My suspicion
was the driver (odbc versus native sql driver. don't ask me why).. If we
ran a query in QA, it ran in sub-second, but when ran through the app it ran
in 3 to 4 seconds. Exact same parameters and select etc.
I ended up changing the query in the procedure and did a pre-select and got
both to run in the same time frame.
I know this doesn' t make sense, but have you tried dropping and re-creating
the index'
Bill
"Richard Douglass" <RichardD@.arisinc.com> wrote in message
news:e2ncx%23jNFHA.3380@.TK2MSFTNGP15.phx.gbl...
> Nothing has changed. The code is stable and has not been modified in
> almost
> a year.
> It makes no sense that the SELECT in the job runs for 3000 milliseconds
> and
> a cut-n-paste of the same query runs in 40 milliseconds in query analyzer.
> Both the job and QA produce the exact same execution plan (Same logical
> look, both tables index sing)
> "KH" <KH@.discussions.microsoft.com> wrote in message
> news:7DEC1462-2C11-4340-BAA2-8DD92B47C0C5@.microsoft.com...
> had
> hours.
> to
> are
> tables
> is
> (As
>|||Have you tried updating the stats on those two tables? You might have
gotten a bad query plan that is being reused by the job. When you run the
query individually it will get it's own plan. Also make sure that you have
SET NOCOUNT ON set at the beginning of the code in the job.
Andrew J. Kelly SQL MVP
"Richard Douglass" <RichardD@.arisinc.com> wrote in message
news:ukuptfjNFHA.2136@.TK2MSFTNGP14.phx.gbl...
>I am experience a very strange performance problem. A nightly job that had
> been consistently running 2 hours each night is suddenly running 16 hours.
> A trace reveals a section of code taking 2500-3800 milliseconds of CPU to
> process. The execution plan for the select statement shows the indexes
> are
> correctly being selected and an index s is being performed on both
> tables
> in the select. There are approx 400,000 rows, and the trace says it is
> reading every row (although index sing)
> If I run the same select statement in query analyzer while the Agent job
> is
> running it processes in about 35 milliseconds, reading less than 60 rows
> (As
> it should)
> Any ideas on why I am getting such a large performance gap'
> help!
> Thanks
> Richard Douglass
>|||"Richard Douglass" <RichardD@.arisinc.com> wrote in message
news:e2ncx%23jNFHA.3380@.TK2MSFTNGP15.phx.gbl...
> Nothing has changed. The code is stable and has not been modified in
> almost
> a year.
> It makes no sense that the SELECT in the job runs for 3000 milliseconds
> and
> a cut-n-paste of the same query runs in 40 milliseconds in query analyzer.
> Both the job and QA produce the exact same execution plan (Same logical
> look, both tables index sing)
It sound very much like a problem we're having on one of our projects here
as well!
Sometime during easter the performance on one og our Stored Procedures has
gone VERY bad!
Our code is also stable and has not been modified during since some time
before easter!
With the Profiler we can see that the SP call from the webapplication takes
approx. 6 seconds - but if we run the same SP call from QA it takes approx.
second!
We suspected an applied MDAC 2.8 update that had been applied during easter
to be the source of the problem, but after an uninstall and restart it's
still slow!
Are there any more people that has experienced this problem out there? And
if any should have solved the problem please explain thoroughly!
Cheers
Rene