Microsoft SQL Server forum - SQL server

Author

SQL server - script execution time

iam980@gmail.com

2005-11-22, 11:23 am

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

2005-11-22, 11:23 am

Am 22 Nov 2005 07:57:33 -0800 schrieb iam980@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

2005-11-22, 8:23 pm

(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/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Trevor Best

2005-11-28, 3:24 am

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

Sponsored Links