Executing a Stored Procedure via Query Analysier is faster then a

I have a complicated Stored Procedure that when run as a Scheduled Job takes
9 hours to complete. When I run the same Stored Procedure in Query Analysier,
it takes 3 hours.
The box is a Quad Xeon 3.0 Ghz w/E64MT and 8 GB of ram attached to a IBM
FiberChannel SAN. It is part of a Windows 2003 / SQL 2000 Cluster.
I run the job every night at the same time, thus the server load should be
comparable.
Is there a fundamental difference in how Query Analyser executes a Stored
Procedure and how a SQL Job is executed?
This Stored Proc uses Temp Tables and Indexes heavily.
We've reindexed the database, recompiled the stored procs and truncated log
tables. The times are still wildly different between the two execution types.
I just don't know where to start / continue troubleshooting.
Please Advise.
- Davidyou can break it in to more steps ( like inserting getdate() or printing the
name of the step) and direct the output to a file. Then if you compare the
files you can determine the steps which are taking more time and try to find
the reason by analyzing the lock conflicts, wait, parallel processes etc..
"David Hekimian" wrote:
> I have a complicated Stored Procedure that when run as a Scheduled Job takes
> 9 hours to complete. When I run the same Stored Procedure in Query Analysier,
> it takes 3 hours.
> The box is a Quad Xeon 3.0 Ghz w/E64MT and 8 GB of ram attached to a IBM
> FiberChannel SAN. It is part of a Windows 2003 / SQL 2000 Cluster.
> I run the job every night at the same time, thus the server load should be
> comparable.
> Is there a fundamental difference in how Query Analyser executes a Stored
> Procedure and how a SQL Job is executed?
> This Stored Proc uses Temp Tables and Indexes heavily.
> We've reindexed the database, recompiled the stored procs and truncated log
> tables. The times are still wildly different between the two execution types.
> I just don't know where to start / continue troubleshooting.
> Please Advise.
> - David|||I've done that, but the log generated is 900MB :) Its taking me a while to
figure out what's going on...
There should be no reason why running the job in Query Analyzer should
execute 3x faster then if it is scheduled to run as a job.
I'm wondering if there is something fundamentally different in the way the
execution of the jobs is being handled. Could running via Query Analyzer use
a different Execution Plan vs the Job?
If so, How do I get SQL to pick the faster Execution Plan when running as a
Job?
If the Stored Procedure was taking longer using both methods, I'd look at
trying to enhance the Stored Procedure... but if the same Stored Procedure is
being executed with such a difference in timing, then something must be
misconfigured or broken.
- David|||try including some insert statements ( at different steps) into a temp tables
with step_name and date_inserted. we can just compare the times then.
"David Hekimian" wrote:
> I've done that, but the log generated is 900MB :) Its taking me a while to
> figure out what's going on...
> There should be no reason why running the job in Query Analyzer should
> execute 3x faster then if it is scheduled to run as a job.
> I'm wondering if there is something fundamentally different in the way the
> execution of the jobs is being handled. Could running via Query Analyzer use
> a different Execution Plan vs the Job?
> If so, How do I get SQL to pick the faster Execution Plan when running as a
> Job?
>
> If the Stored Procedure was taking longer using both methods, I'd look at
> trying to enhance the Stored Procedure... but if the same Stored Procedure is
> being executed with such a difference in timing, then something must be
> misconfigured or broken.
> - David
>|||David,
Does the stored procedure have SET NOCOUNT ON at the beginning? If not then
try adding it. I have seen jobs do just what you describe by not having SET
NOCOUNT ON in them.
--
Andrew J. Kelly SQL MVP
"David Hekimian" <David Hekimian@.discussions.microsoft.com> wrote in message
news:F71CDFA4-1822-410B-9F4A-696C6BE4D7F3@.microsoft.com...
>I have a complicated Stored Procedure that when run as a Scheduled Job
>takes
> 9 hours to complete. When I run the same Stored Procedure in Query
> Analysier,
> it takes 3 hours.
> The box is a Quad Xeon 3.0 Ghz w/E64MT and 8 GB of ram attached to a IBM
> FiberChannel SAN. It is part of a Windows 2003 / SQL 2000 Cluster.
> I run the job every night at the same time, thus the server load should be
> comparable.
> Is there a fundamental difference in how Query Analyser executes a Stored
> Procedure and how a SQL Job is executed?
> This Stored Proc uses Temp Tables and Indexes heavily.
> We've reindexed the database, recompiled the stored procs and truncated
> log
> tables. The times are still wildly different between the two execution
> types.
> I just don't know where to start / continue troubleshooting.
> Please Advise.
> - David|||"David Hekimian" <David Hekimian@.discussions.microsoft.com> wrote in message
news:F71CDFA4-1822-410B-9F4A-696C6BE4D7F3@.microsoft.com...
> I have a complicated Stored Procedure that when run as a Scheduled Job
takes
> 9 hours to complete. When I run the same Stored Procedure in Query
Analysier,
> it takes 3 hours.
> The box is a Quad Xeon 3.0 Ghz w/E64MT and 8 GB of ram attached to a IBM
> FiberChannel SAN. It is part of a Windows 2003 / SQL 2000 Cluster.
> I run the job every night at the same time, thus the server load should be
> comparable.
> Is there a fundamental difference in how Query Analyser executes a Stored
> Procedure and how a SQL Job is executed?
> This Stored Proc uses Temp Tables and Indexes heavily.
> We've reindexed the database, recompiled the stored procs and truncated
log
> tables. The times are still wildly different between the two execution
types.
> I just don't know where to start / continue troubleshooting.
> Please Advise.
> - David
The SP may be running as different users in Query Analyser and as a Job, but
I've never seen this affect performance.
Can you reproduce the time differential by running the SP against a reduced
data set? - it's a bit difficult to debug issues like this when each test
takes 9 hours.
Regards,
Simon