Procedure performance in QA vs Sql Agent Job

[Tracey]

I am having a problem with a procedure. I can run it from QA and it
takes 50 minutes. When I have it in a scheduled job, it takes 3
hours!! What could be the cause of this? Why the big time
difference?

Thanks

[Erland Sommarskog]

Tracey (tr**********@itsservices.com) writes:

I am having a problem with a procedure. I can run it from QA and it
takes 50 minutes. When I have it in a scheduled job, it takes 3
hours!! What could be the cause of this? Why the big time
difference?

The difference could be due to that Query Analyzer sets some SET options
on, which are not on by default with SQL Agent. Different set of SET
options, gives you a different query plan.

The difference could be due do that the query plans were created for
different input parameters, and therefore were different.

More specifically, it could be that there is an indexed view or an index
on a computed column involved. To use these features, all these
settings must be on: ANSI_NULLS, ANSI_WARNINGS, ANSI_PADDING,
QUOTED_IDENTIFIER, CONCAT_NULL_YIELDS_NULL and ARITHABORT. The mostly
likely culprit is ARITHABORT ON. You could try adding a SET ARITHABORT
ON first in the procedure, and see if this improves the execution time
when running from Agent.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[Tracey]

tr**********@itsservices.com (Tracey) wrote in message news:<b2**************************@posting.google. com>...

I am having a problem with a procedure. I can run it from QA and it
takes 50 minutes. When I have it in a scheduled job, it takes 3
hours!! What could be the cause of this? Why the big time
difference?

Thanks

Thanks for your suggestions. The time it took for the procedure to
run was still the same. I suppose trying sql profiler might offer
some clues, yes?

[Erland Sommarskog]

Tracey (tr**********@itsservices.com) writes:

tr**********@itsservices.com (Tracey) wrote in message

news:<b2**************************@posting.google. com>...

I am having a problem with a procedure. I can run it from QA and it
takes 50 minutes. When I have it in a scheduled job, it takes 3
hours!! What could be the cause of this? Why the big time
difference?

Thanks for your suggestions. The time it took for the procedure to
run was still the same. I suppose trying sql profiler might offer
some clues, yes?

Please, post you reply as a followup to the article you respond to,
rather than you own. This makes it a little easier to reconstruct the
thread.

You could explore more of the options I discussed in my post.

It's a little difficult to give more advice when I have no idea of
the code you are running.

But Profiler could help you to narrow down where the slowdown sets in,
yes.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp