Slow stored procedures

[Cory]

Hi,

I have a stored procedure that normalizes data from one database to
another that is included in a SQL Agent job. The job is started by a
trigger so that when a table is updated, the job to normalize data
executes.

This happens once a day in the dead of the night when nothing else is
happening and the stored procedure takes 2.5 hours to finish. Strangely,
to run the same stored procedure from Query Analyzer takes only 20
minutes to do the same thing. The job is executed as the same user that
logs in to Query Analyzer.

Why does running as a job take so much longer than running it manually.
At the time the job is run, there is absolutely nothing else to create
waits or deadlocks. A profiler trace shows that each statement runs
quickly but then it sits and twiddles its thumbs between statements.
Performance monitor shows little disk activity and only 2% to 5%
processor utilization.

Any ideas?

[Simon Hayes]

I have no real idea, but perhaps if you give some more information
someone will be able to suggest something - are you executing exactly
the same command in the job, or are you using different parameters in
QA, for example? When you say that Profiler shows the proc "twiddling
its thumbs", what does that actually mean - what event types are you
seeing? Is the proc doing something unusual, such as a BULK INSERT, or
just modifying data? Are any other jobs or processes running on the
server at the same time? Etc.

Simon