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.
- David
you 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
|||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