Mrdenny 
46795 pts. | Sep 9 2008 9:48AM GMT
Check out my SQL Server blog “SQL Server with Mr Denny” for more SQL Server information.
Q:
Running a stored procedure in SQL Query Analyzer or as part of a SQL job
I have been trying to run a stored procedure in SQL Query Analyzer and as a step in a SQL job. If I run it in Query Analyzer, it takes less than a minute, but it takes considerably longer if it is run as a step. I use SQL Server 2000. Why would there be so big a difference?
ASKED:
Jul 11 2008 2:52 PM GMT
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _
A:
RATE THIS ANSWER
0
Click to Vote:
0
Click to Vote:
- 0
0
Setup SQL Profiler and have it monitor the SQL Server, then run the job. Have SQL Profiler give you the execution plan which is being used so that you can see exactly what it's doing.
You may find that you need to add the same ANSI Settings to the SQL Agent job.
It could also be because database maintenance is being performed at the time that the job is running, and that maintenance is not being run when you run the procedure during the day. Check for other jobs which are running at the same time as the SQL Agent job which is having the performance problem.
