April 16, 2009 at 9:02 am
SQL2000 SP3/4 on IBM quad-core servers w/ 32GB memory.
We have a one-line script that runs a stored procedure:
exec sprocname
It's running much slower than we think it should. After looking into it for a while, we've narrowed it down to SQL Agent. When running that line in a SQL Agent job, it processes X rows/second. When eecuting the same line from Query Analyzer, it processes 20X rows/second. This is repeatable, i.e. we've run it in both places multiple times, with the same results every time.
We've dropped the SQL Agent job and re-created it. We've tried it on multiple servers, SP3, SP4, and hot-fixed SP4.
How could SQL Agent be "interfering with" (slowing down) a procedure once it has started that procedure? Are there resources that SQL Agent needs to run a procedure that aren't needed by running the procedure in QA?
We also tried it on a SQL2005 test box -- there SQL Agent ran at the same speed as running it manually SSMS. So whatever it is, it appears to have changed between SQL2000 and SQL2005.
Thanks!
April 17, 2009 at 2:12 pm
Sorry to reply to my own thread, but we figured this out today. SQL Agent in SQL2000 apparently cannot deal with the returns from the SQL statements when NOCOUNT is set to OFF. We put a SET NOCOUNT ON in the procedure and it took off.
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply