Poor SQL Server Performance

Hello,
I have a big problem with the performance of SQL Server 2000 on an IBM
eSeries 335, with one Intel Xeon 2,4 GHz CPU and 1 GB RAM. As OS I tried:
Windows Server 2003
Windows Server 2000
Windows 2000 Professional
To insert of 6400 lines needs around 28 seconds. It doesn`t matter on
witch OS the SQL Server 2000 is installed. My desktop system with
Windows XP Professional, Windows 2000 Professional and SQL Server 2000
needs for the same datas just 8 seconds.
I supose the SQL Server 2000 may have a problem with the hyperthreading
of the Xeon CPU. I have configured the SQL Server 2000 to use only one
CPU but it has no effects to the performance of the SQL Server 2000.
Is there any possibility to disable the hyperthreading within the OS?
Hope somebody an help.
Cu
Thomas M.There are a lot of factors in how long it takes to do something like an
insert. What is the client and how are you actually doing the inserts?
What is the DDL of the table like and a typical row look like? Do you have
your log file on a separate RAID 1 from anything else?
--
Andrew J. Kelly
SQL Server MVP
"Thomas Meinke" <thomas.meinke@.arcord.de> wrote in message
news:1hhs9ollnxc5x$.dzb5as2qlncf.dlg@.40tude.net...
> Hello,
> I have a big problem with the performance of SQL Server 2000 on an IBM
> eSeries 335, with one Intel Xeon 2,4 GHz CPU and 1 GB RAM. As OS I tried:
> Windows Server 2003
> Windows Server 2000
> Windows 2000 Professional
> To insert of 6400 lines needs around 28 seconds. It doesn`t matter on
> witch OS the SQL Server 2000 is installed. My desktop system with
> Windows XP Professional, Windows 2000 Professional and SQL Server 2000
> needs for the same datas just 8 seconds.
> I supose the SQL Server 2000 may have a problem with the hyperthreading
> of the Xeon CPU. I have configured the SQL Server 2000 to use only one
> CPU but it has no effects to the performance of the SQL Server 2000.
> Is there any possibility to disable the hyperthreading within the OS?
> Hope somebody an help.
> Cu
> Thomas M.|||On Fri, 31 Oct 2003 07:51:12 -0500, Andrew J. Kelly wrote:
> There are a lot of factors in how long it takes to do something like an
> insert. What is the client and how are you actually doing the inserts?
> What is the DDL of the table like and a typical row look like? Do you have
> your log file on a separate RAID 1 from anything else?
Hello,
the client is a .Net-Application, that uses the sql ADO.NET drivers. The
inserts are made by calling a stored procedure.
Here is the sql create syntax for the stored procedure:
CREATE PROCEDURE sp_teilenummer_insert
@.tln_pk_tln_vchar VARCHAR(10),
@.tln_tind_vchar VARCHAR(2),
@.tln_bng_vchar VARCHAR(40),
@.wgp_fk_wgpid_vchar VARCHAR(10),
@.tln_tart_vchar VARCHAR(20),
@.tln_tstat_vchar VARCHAR(2)
AS
INSERT INTO tbl_liste_teilenummer(
tln_pk_tln_vchar,
tln_tind_vchar,
tln_bng_vchar,
wgp_fk_wgpid_vchar,
tln_tart_vchar,
tln_tstat_vchar
)
VALUES (@.tln_pk_tln_vchar,
@.tln_tind_vchar,
@.tln_bng_vchar,
@.wgp_fk_wgpid_vchar,
@.tln_tart_vchar,
@.tln_tstat_vchar
)
The amazing thing is that the same software configuration on my development
machine is so much faster than the server machine with much better
hardware. My development machine is an Intel Pentium III with 1 GHz and 512
MB RAM. It is the same application with the same datas but its poor slow on
the server machine.
Cu
Thomas M.|||Isn't it simply because you add network overhead? If you insert row by row
you add 6400 network roundtrips to what you might have tested locally on
your XP machine.
--
regards,
Mario
http://www.sqlinternals.com
"Thomas Meinke" <thomas.meinke@.arcord.de> wrote in message
news:1107459esxrhn.f67d4a2opmrw.dlg@.40tude.net...
> On Fri, 31 Oct 2003 07:51:12 -0500, Andrew J. Kelly wrote:
> > There are a lot of factors in how long it takes to do something like an
> > insert. What is the client and how are you actually doing the inserts?
> > What is the DDL of the table like and a typical row look like? Do you
have
> > your log file on a separate RAID 1 from anything else?
> Hello,
> the client is a .Net-Application, that uses the sql ADO.NET drivers. The
> inserts are made by calling a stored procedure.
> Here is the sql create syntax for the stored procedure:
> CREATE PROCEDURE sp_teilenummer_insert
> @.tln_pk_tln_vchar VARCHAR(10),
> @.tln_tind_vchar VARCHAR(2),
> @.tln_bng_vchar VARCHAR(40),
> @.wgp_fk_wgpid_vchar VARCHAR(10),
> @.tln_tart_vchar VARCHAR(20),
> @.tln_tstat_vchar VARCHAR(2)
> AS
> INSERT INTO tbl_liste_teilenummer(
> tln_pk_tln_vchar,
> tln_tind_vchar,
> tln_bng_vchar,
> wgp_fk_wgpid_vchar,
> tln_tart_vchar,
> tln_tstat_vchar
> )
> VALUES (@.tln_pk_tln_vchar,
> @.tln_tind_vchar,
> @.tln_bng_vchar,
> @.wgp_fk_wgpid_vchar,
> @.tln_tart_vchar,
> @.tln_tstat_vchar
> )
> The amazing thing is that the same software configuration on my
development
> machine is so much faster than the server machine with much better
> hardware. My development machine is an Intel Pentium III with 1 GHz and
512
> MB RAM. It is the same application with the same datas but its poor slow
on
> the server machine.
> Cu
> Thomas M.|||Are you comparing like with like? I.E It sounds like in your development
case your client and the db are on the same machine, whereas for production
the client and the db are on different machines?
--
Niall Litchfield
Oracle DBA
Audit Commission Uk
"Thomas Meinke" <thomas.meinke@.arcord.de> wrote in message
news:1107459esxrhn.f67d4a2opmrw.dlg@.40tude.net...
> On Fri, 31 Oct 2003 07:51:12 -0500, Andrew J. Kelly wrote:
> > There are a lot of factors in how long it takes to do something like an
> > insert. What is the client and how are you actually doing the inserts?
> > What is the DDL of the table like and a typical row look like? Do you
have
> > your log file on a separate RAID 1 from anything else?
> Hello,
> the client is a .Net-Application, that uses the sql ADO.NET drivers. The
> inserts are made by calling a stored procedure.
> Here is the sql create syntax for the stored procedure:
> CREATE PROCEDURE sp_teilenummer_insert
> @.tln_pk_tln_vchar VARCHAR(10),
> @.tln_tind_vchar VARCHAR(2),
> @.tln_bng_vchar VARCHAR(40),
> @.wgp_fk_wgpid_vchar VARCHAR(10),
> @.tln_tart_vchar VARCHAR(20),
> @.tln_tstat_vchar VARCHAR(2)
> AS
> INSERT INTO tbl_liste_teilenummer(
> tln_pk_tln_vchar,
> tln_tind_vchar,
> tln_bng_vchar,
> wgp_fk_wgpid_vchar,
> tln_tart_vchar,
> tln_tstat_vchar
> )
> VALUES (@.tln_pk_tln_vchar,
> @.tln_tind_vchar,
> @.tln_bng_vchar,
> @.wgp_fk_wgpid_vchar,
> @.tln_tart_vchar,
> @.tln_tstat_vchar
> )
> The amazing thing is that the same software configuration on my
development
> machine is so much faster than the server machine with much better
> hardware. My development machine is an Intel Pentium III with 1 GHz and
512
> MB RAM. It is the same application with the same datas but its poor slow
on
> the server machine.
> Cu
> Thomas M.|||On Fri, 31 Oct 2003 14:16:04 +0100, Mario wrote:
> Isn't it simply because you add network overhead? If you insert row by row
> you add 6400 network roundtrips to what you might have tested locally on
> your XP machine.
Hello,
I don`t think so, because I have installed the .NET Application on the
server to test it. And the application needs the same time to insert the
data as over the network.
I tried to insert the datas into another SQL Server 2000 on another
developer machine over network and it needs the same time as locally on
my machine.
Cu
Thomas M.|||6,400 inserts in 28 sec for 228 inserts/sec sounds like
the log disk bottleneck to me.
for each insert, sql svr is waiting for the prev insert
log write to complete.
In Computer Management -> Disk Management, right click on
the physical disk (disk 0, etc) go to the Policies tab,
try various settings of write cache enabled, adv. perf etc.
you can do this while your insert test is running, be sure
to monitor disk writes/sec
>--Original Message--
>Hello,
>I have a big problem with the performance of SQL Server
2000 on an IBM
>eSeries 335, with one Intel Xeon 2,4 GHz CPU and 1 GB
RAM. As OS I tried:
>Windows Server 2003
>Windows Server 2000
>Windows 2000 Professional
>To insert of 6400 lines needs around 28 seconds. It
doesn`t matter on
>witch OS the SQL Server 2000 is installed. My desktop
system with
>Windows XP Professional, Windows 2000 Professional and
SQL Server 2000
>needs for the same datas just 8 seconds.
>I supose the SQL Server 2000 may have a problem with the
hyperthreading
>of the Xeon CPU. I have configured the SQL Server 2000 to
use only one
>CPU but it has no effects to the performance of the SQL
Server 2000.
>Is there any possibility to disable the hyperthreading
within the OS?
>Hope somebody an help.
>Cu
>Thomas M.
>.
>|||If you are the only one one the test machine the following might show where
you 'loose' time:
if you test program is logged on: select cpu from sysprocesses
do a dbcc sqlperf(waitstats,clear)
do your test run
now a dbcc sqlperf(waitstats)
check sysprocesses.cpu for the spid that ran the test run
the overall resource consumption (cpu+wait+sigwait) should be about the
elapsed time of your testrun.
If you do the same on the 'fast' host, you should see a difference
somewhere: either in cpu consumption or on one of the waitstats.
(a sqlprofiler run might be more easy to do, but doesn;t show wait
statistics, which can reveal your current problem)
--
regards,
Mario
http://www.sqlinternals.com
"Thomas Meinke" <thomas.meinke@.arcord.de> wrote in message
news:1uzrvpac6ff4d$.1un4vediy6zql$.dlg@.40tude.net...
> On Fri, 31 Oct 2003 14:16:04 +0100, Mario wrote:
> > Isn't it simply because you add network overhead? If you insert row by
row
> > you add 6400 network roundtrips to what you might have tested locally on
> > your XP machine.
> Hello,
> I don`t think so, because I have installed the .NET Application on the
> server to test it. And the application needs the same time to insert the
> data as over the network.
> I tried to insert the datas into another SQL Server 2000 on another
> developer machine over network and it needs the same time as locally on
> my machine.
> Cu
> Thomas M.|||"joe chang" <anonymous@.discussions.microsoft.com> wrote in message
news:0b1801c39fb9$ce6c8350$a401280a@.phx.gbl...
> 6,400 inserts in 28 sec for 228 inserts/sec sounds like
> the log disk bottleneck to me.
> for each insert, sql svr is waiting for the prev insert
> log write to complete.
> In Computer Management -> Disk Management, right click on
> the physical disk (disk 0, etc) go to the Policies tab,
> try various settings of write cache enabled, adv. perf etc.
> you can do this while your insert test is running, be sure
> to monitor disk writes/sec
>
Good thought. To further reduce log write waits, wrap all of your inserts
in a single transaction.
This will allow you to defer the log writes until the end and do one large
write instead of 6,400 small ones.
David|||The information gets written to the log regardless of if it's in a user
defined transaction or not. There can be a certain amount of overhead
associated with 6400 separate log entries vs 1 but the info is still all
there. You have to be careful with a general statement (unfortunately we
have to do it at times) as that since it can depend on a lot of factors as
to which would be faster. I do agree that the log seems a likely place to
start though. Thomas, is it on a separate disk?
--
Andrew J. Kelly
SQL Server MVP
"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
message news:u4jBJL8nDHA.2068@.TK2MSFTNGP09.phx.gbl...
> "joe chang" <anonymous@.discussions.microsoft.com> wrote in message
> news:0b1801c39fb9$ce6c8350$a401280a@.phx.gbl...
> > 6,400 inserts in 28 sec for 228 inserts/sec sounds like
> > the log disk bottleneck to me.
> > for each insert, sql svr is waiting for the prev insert
> > log write to complete.
> > In Computer Management -> Disk Management, right click on
> > the physical disk (disk 0, etc) go to the Policies tab,
> > try various settings of write cache enabled, adv. perf etc.
> > you can do this while your insert test is running, be sure
> > to monitor disk writes/sec
> >
> >
> Good thought. To further reduce log write waits, wrap all of your inserts
> in a single transaction.
> This will allow you to defer the log writes until the end and do one large
> write instead of 6,400 small ones.
> David
>|||"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:OCbW13%23nDHA.1764@.tk2msftngp13.phx.gbl...
> The information gets written to the log regardless of if it's in a user
> defined transaction or not. There can be a certain amount of overhead
> associated with 6400 separate log entries vs 1 but the info is still all
> there.
Yes, but the log is flushed to disk after each of the 6400 inserts unless
they are all in a transaction. With a transaction, the log is flushed on
its normal schedule, or at the latest when the transaction is commited.
David