Posted by Erland Sommarskog on 11/23/05 00:14

(iam980@gmail.com) writes:
> Hello All.
>
> We have tested following SQL script from query analyzer:
> -- Script begin
> DECLARE @I int;
> SET @I = 1;
> WHILE @I < 10000000 BEGIN
> SET @I = @I + 1;
> END
> -- Script end
>...
> I have following question: Is the above mentioned execution time is
> normal for such script ?
> Also it would be very nice of You to run the script on Your SQL servers
> and inform me about execution time results

On my machine it ran for 11 minutes and 15 seconds on SQL 2000 and 15
seconds on SQL 2005.

When I put it into a stored procedure and had SET NOCOUNT ON, it ran for
19 seconds on SQL 2000.

I ran into this some time ago, and first I thought it was an issue with
SP4, but that was a false alert. I think I came to the conclusion that
parallelism was part of the plot. That is, this sort of script typically
runs slower on a multi-CPU machine. (Including hyper-threaded CPU:s).

I also found that with SET NOCOUNT ON *and* the loop in a stored
procedure, that I did not get the absymal performance.




--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Back to original message]