Poor SQL Server Performance on Windows 2003 Server

I'm having a problem with SQL Server 2000 SP4 running on Windows 2003 Server
SP1. The performance is just awful.
I'm running a benchmark that creates a table and inserts 10000 records. This
is an ODBC application written in C++.
The server is a Quad 2.8G XEON with 2G RAM. On this server it takes 46
seconds to run the application that inserts the records.
On my laptop which is a 2.0G Pentium M with 1G RAM the same application
takes 7 seconds. My laptop has WinXP rather than Win2003 Server.
Now if you're not a very astute reader of benchmark results the bottom line
is that it takes 6 times longer to run this test on a high spec server
machine than on my laptop.
This is what I've done so far to try and fix this:
1. Try various configurations of processors and memory in the SQL
server configuration.
2. Reinstall SQL server.
3. Install Win2003 SP1.
4. Investigate hardware conflicts on the machine.
5. Benchmark disk performance. It's about twice as fast as the disk in
my laptop for both sequential and burst access.
So does anyone have any idea why the performance on this high spec server
would be so bad?
Thanks,
Colin
A likely cause is that the server does not have disk write caching on the
SQL Server log but your laptop does. Assuming each insert is an individual
transaction, 10000 log I/Os are required. At a typical rate of 200 physical
disk I/Os per second, this comes to about 50 seconds.
A decent disk controller with write caching (and importantly battery backup)
ought to address your performance issue.
Hope this helps.
Dan Guzman
SQL Server MVP
"Colin Blair" <col_blair@.nospam.hotmail.com> wrote in message
news:eDIS4Ye1FHA.2212@.TK2MSFTNGP15.phx.gbl...
> I'm having a problem with SQL Server 2000 SP4 running on Windows 2003
> Server SP1. The performance is just awful.
>
> I'm running a benchmark that creates a table and inserts 10000 records.
> This is an ODBC application written in C++.
>
> The server is a Quad 2.8G XEON with 2G RAM. On this server it takes 46
> seconds to run the application that inserts the records.
>
> On my laptop which is a 2.0G Pentium M with 1G RAM the same application
> takes 7 seconds. My laptop has WinXP rather than Win2003 Server.
>
> Now if you're not a very astute reader of benchmark results the bottom
> line is that it takes 6 times longer to run this test on a high spec
> server machine than on my laptop.
>
> This is what I've done so far to try and fix this:
>
> 1. Try various configurations of processors and memory in the SQL
> server configuration.
> 2. Reinstall SQL server.
> 3. Install Win2003 SP1.
> 4. Investigate hardware conflicts on the machine.
> 5. Benchmark disk performance. It's about twice as fast as the disk
> in my laptop for both sequential and burst access.
>
> So does anyone have any idea why the performance on this high spec server
> would be so bad?
>
> Thanks,
> Colin
>
|||Thanks for your reply Dan.
I checked the disk write cache option in the drive policies in the Win2003
device manager and it was enabled.
I also checked the SCSI card setup and that was fine too.
I should have also mentioned that the number of rows per transaction is
10000. So there is basically only 1 transaction.
Cheers,
Colin.
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:uL$WGhe1FHA.556@.TK2MSFTNGP12.phx.gbl...
>A likely cause is that the server does not have disk write caching on the
>SQL Server log but your laptop does. Assuming each insert is an individual
>transaction, 10000 log I/Os are required. At a typical rate of 200
>physical disk I/Os per second, this comes to about 50 seconds.
> A decent disk controller with write caching (and importantly battery
> backup) ought to address your performance issue.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Colin Blair" <col_blair@.nospam.hotmail.com> wrote in message
> news:eDIS4Ye1FHA.2212@.TK2MSFTNGP15.phx.gbl...
>
|||What kind of drive is this in the Win2003 Server? Is it a drive array or
just a drive? Where are the log files located? A typical drive in a laptop
has more cache than one you normally find in a server. It can be several
times more in fact and this can make a huge difference when you have lots of
writes such as this. You also want the log drive on a physically separate
(not logically) drive than the data file. Even though the cache may be
enabled it may not be 100% write back. Normally they come as a 50/50 or
60/40 split between read and write cache. You can get a big performance
boost on writes by changing it to 100% write back.
Andrew J. Kelly SQL MVP
"Colin Blair" <col_blair@.nospam.hotmail.com> wrote in message
news:OauuqrD2FHA.2072@.TK2MSFTNGP14.phx.gbl...
> Thanks for your reply Dan.
> I checked the disk write cache option in the drive policies in the Win2003
> device manager and it was enabled.
> I also checked the SCSI card setup and that was fine too.
> I should have also mentioned that the number of rows per transaction is
> 10000. So there is basically only 1 transaction.
> Cheers,
> Colin.
> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
> news:uL$WGhe1FHA.556@.TK2MSFTNGP12.phx.gbl...
>
|||It seems a bit suspect that your throughput is as if each insert was in an
individual transaction. The log i/o during the COMMIT is synchronous so
your app will wait for completion. How are you doing the inserts?
Hope this helps.
Dan Guzman
SQL Server MVP
"Colin Blair" <col_blair@.nospam.hotmail.com> wrote in message
news:OauuqrD2FHA.2072@.TK2MSFTNGP14.phx.gbl...
> Thanks for your reply Dan.
> I checked the disk write cache option in the drive policies in the Win2003
> device manager and it was enabled.
> I also checked the SCSI card setup and that was fine too.
> I should have also mentioned that the number of rows per transaction is
> 10000. So there is basically only 1 transaction.
> Cheers,
> Colin.
> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
> news:uL$WGhe1FHA.556@.TK2MSFTNGP12.phx.gbl...
>