Slow SQL/NIC performance

[Kenneth McNeil]

Problems with SQL 2000 performance in returning query results to applications running on the network.

Configuration:

Production Server:
Compaq ML-530 Dual P-4 Xeon, Broadcom 3c996B-T, 3GB Ram
5300 SCSI Controller: 0/1 Mirror (CH1), 2/3 Mirror (CH1), 4/5/6/7/8 Raid 5 (CH2)
MS Windows 2000 Server SP3
MS SQL 2000 Server (Standard) SP 3 (1.7GB allocated)
Hyper-Threading disabled in BIOS and Enabled for some tests.

Test Server:
Intel Single P4 (non-Xeon), generic 100 base NIC, 1GB Ram
SX-6000 6 drive IDE Raid controller: 0/1 Mirror, 2/3/4/5 Raid 5
MS Windows 2000 Server SP3
MS SQL 2000 Server (Standard) SP 3 (750MB allocated)

Network:
Dell Managed Gig-switches
Dell Managed 10/100 switches

Application ??? Power Builder Account Management Application

When running reports from workstations we get the following results:
Production: 70 reports per minute
Test: 380 reports per minute

When running the reports on each server (application running on server) we get the following results:
Production: 925 reports per minute
Test: 675 reports per minute

With these test results, we clearly indicate that the Production server is able to provide the higher performance only when not utilizing the network.

We have tried this testing with different network switches, different workstations, and different settings for Hyper-Threading, SQL Server Priorities, Number of CPU???s for parallel tasks, and different network cables.

Performance Analysis ??? Production Server:
Perfmon does not indicate a problem with the disk Queue on any of the drives, does not indicate a memory problem with MS SQL, and does not indicate any problems that we can determine.

Our indication is that there is a performance issue with the Compaq ML-530 PCI Bus and network cards of any type. We have arrived at this belief by testing all possible combinations of cards, slots, local, remote, and perfmon analysis.

[ron fisher_1]

We run PB apps in a thin client server based thin client environment. I assume you're running PB client on the workstations and connect to SQL via ODBC? How long does it take for ODBC to connect?

Have you tried single nic port config rather than teamed? Seeing any CRC errors on the switch ports or in the NIC teaming config? How about boot up errors in the eventlog related to the nic not negotiating properly.

What are your duplex/speed settings on both side? Any chance for mis-match? Have you tried Link Aggregation teaming on the nic? You can't use Etherchannel since you're on Dell switches..

[Kenneth McNeil]

Thanks Ron for your reply,

To answer your questions,

Yes we are running PB client on the workstations and connecting to SQL via ODBC.
No we have not tested the ODBC connection

We are currently using a single 3com gb nic, we prefer this over using teamed.
No errors on the nic or at boot. The Nic is running at full duplex gb, which is done automatically, and cannot be done manually.

The issue we believe is not the nic, since we get the same results with the 3com, as well as with the on-board nic after testing (read previous message), the issue has to be I/O related, hence the look at the PCI bus. There is a bottleneck when we try to run these processes over the network, as compared to running them on the ML530. This bottleneck especially came to light when we ran the same processes across the network with the test server. So our main focus is still the I/O bus, and not the NIC's.

[ron fisher_1]

Well your subject line would indicate otherwise. I suspect you'll have a hard time finding answers specific to motherboard I/O here. Have you taken it up with HP directly?

[Kenneth McNeil]

Thanks again for your reply,

I was trying to present the question from as many angles as I could, SQL, NIC, and I/O, just trying to see if any of these problems have been seen by hp/compaq users.

I will be flashing the BIOS, and installing upgraded drivers this weekend hoping that will fix the problem.

I have called HP but they have seen no specific I/O issues with this server. Hopefully the drivers will fix the issue.