20 November 2008 07:29
tonyrogerson
SQL 2000 - Why it takes 1 second in QA and 2 hours under SQLAgent
Luckily this is fixed in 2005 (and I presume 2008) so only applies to SQL 2000 but for the multitudes of people still with 2000 this may help.
Consider the SQL below...
create proc test_prints
as
begin
declare @i int
set @i = 1
while @i <= 50000
begin
print 'This is some output from my stored procedure'
set @i = @i + 1
end
end
This runs in no time at all under Query Analyser, but - now, schedule the stored procedure and execute it under SQLAgent.
It runs, and runs, and runs - mmm (exactly); now, look at the amount of CPU SQLAgent is taking - 100% of one of the available cores.
No, not nice.
When logging - use a table rather than PRINT statements; its a general good practice to do that anyway.