SP performance slow when calling directly on server

[mariuszb]

I use job executing stored procedures to get data for OLAP. All works fine except one thing: one of the procedures takes a few hours to complete. Executing procedure via remote desktop and query analyzer directly on the server gives the same behaviour.

Same procedure executed from query analyzer/management studio from PC works fine - executes in about five minutes.

SQL server 2k. SP has set nocount, estimated execution plans have same steps.

SP gets data from different instance of sql on the same machine, then does a few updates, and finally inserts data to the table.

What can cause such behaviour?

[debasisdas]

Does that involve any locks ?

[mariuszb]

Does that involve any locks ?

Job was deadlocked once, so yes, locks are involved, but i didn't investigate it.