executing set of SPs as joB is hanging..

I have a job which is set of few Stored procedures,Usually it taked around 3-5 mins to complete the job.But somehow today the job was still executing even after 3:45:24 (yes 3 hrs,45 mins 25 secs)
WHen i tried to run the each procedure indivdually even its taking more time in the query analyzer.But when i try to execute those SPS as individual sql statements(it's step by step) they were working in reasonable time.What should be the reason for these SPs taking that much time?

Thanks.ANd also my sql server agent had an error few hrs back as

Name:Demo:Sev. 24 Errors
Type: sQL Server event alert
severity:024-FatalError:Hardware error

Thnaks.|||And one more thing is that other jobs were running fine.|||When i investigate in depth i found that there were lots of locks on that table.How can i solve that problem|||how can i forcibly logout a user(sqlserver user) who has locked the table.|||To detect locks look into master.dbo.syslockinfo

i.e. simple script as example

select distinct object_name(rsc_objid) as Table_name,
case rsc_type when 5 then 'page lock' when 6 then 'table lock' end as lock_level ,case req_mode when 5 then 'X' when 8 then 'IX' end as lock_type , case req_ownertype when 1 then 'transaction' when 2 then 'cursor' when 3 then 'session' when 4 then 'ExSession' end as Owner_type, p.last_batch
from master.dbo.syslockinfo s, master.dbo.sysprocesses p
where exists
(select 1 from master.dbo.sysdatabases where s.rsc_dbid=dbid
and name='yourDBNAME') -- put the name of your db here
and rsc_type in (6)-- table level lock
and req_mode in (5) -- exclusive lock
and req_status=1 --granted lock
and s.req_spid=spid
and object_name(rsc_objid) is not null

to kill a process - look up KILL in BOL.

However, before you do anything that drastic - use Profiler to detect what exactly is going on, find what code is causing the performance degradation and act based on that.

simas

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

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