July 11, 2008 at 6:50 am
I try to run the same stored procedure in Query Analyzer and as a “Step” in SQL Jobs.
If I Run in Query Analyzer duration is less than 1 minute.
As a step in a job duration increase 30 times.
The operating system is Win32 Ent.Edition +Sp2
The Database Server is MSSQL2000 Ent.Edition+Sp4
Thank you !
July 11, 2008 at 9:24 am
Here is my cent.
How many times did you run your job in QA? Only the first time when you run your job in QA may give you correct information. After the first time, the system saves your estimated execution plan in cache. It will be much faster when you run it again.
July 11, 2008 at 9:26 am
I found the solution!
Thank you again !:)
July 11, 2008 at 9:28 am
What else is happening on the server when the job is running? Are there other jobs running that access the same resources? Are there backups running?
Without details on what the sp is doing it is hard to determine why it is faster at times.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
July 11, 2008 at 9:30 am
I tried "SET NOCOUNT ON" in stored procedure and the result is the same as in QA:)
January 21, 2009 at 9:34 am
the same worked for me. I assume it's because the SP being called is generating a lot of "(1 row(s) affected)" lines (over 20,000 in my case) which SQL Server Agent tries to put in the job history.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply