SQL server - script execution time

[iam980]

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

The script was tested on the folowing PCs with following results:
PC 1:
Pentium 4:
CPU: 1.6 Mhz
RAM: 256 Mb
HDD: 80 Gb (IDE)
OS: Windows Advanced Server SP4
SQL: Developer Edition SP4

Result: Script execution time - 0:54

PC 2:
IBM X445, 4 XEON 3.0 Ghz, 4 Mb L3 Cache
RAM: 8 Gb
RAID 5 with (SCSI) 15k disks
OS: Windows 2000 Advanced server SP4
SQL: Enterprise Edition SP 4 + Fix

Result: Script execution time - 2:19

PC 3:
DELL Power edge 1600, 2 XEON 2.40 Ghz,
RAM: 1 Gb
RAID 1 with (SCSI) 10k disks
OS: Windows 2003 Enterprise Edition SP1
SQL: Enterprise Edition SP 4 + Fix

Result: Script execution time - 1:16

NOTE 1:
All PCs have latest hardware updates

NOTE 2:
PC 1 - is local pc, with no external connections
PC 2 - is server with some number of other, external connections;
PC 3 - is server with a few external connections;

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

Thank You beforehand

[helmut woess]

Am 22 Nov 2005 07:57:33 -0800 schrieb ia****@gmail.com:

DECLARE @I int;
SET @I = 1;
WHILE @I < 10000000 BEGIN
SET @I = @I + 1;
END

In the QueryAnalizer on my cheap A64/3000+ it needs 23 seconds to finish.

bye,
Helmut

[Erland Sommarskog]

(ia****@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, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[Trevor Best]

1 x XP2000+ 512MB 27 seconds 100% CPU usage.
2 x MP2000+ 1GB - gave up after 6 minutes, 0% CPU usage.