SQL Server / Adaptec 320 - Poor performance

[aviw]

Hi al,
I read the very interesting article http://www6.tomshardware.com/storage/20030606/index.html about databases and SCSI Ultra 320 controllers. The article indicated that the performance was great for Xeon / SCSI machines. This only tells me that my tests (supplied below) had some configuration problem.

I used Dual Xeon machine with 1GB Ram
SCSI 320 Ultra (and also tried LSI logic)
SQL Server 2000 sp 3a
Windows 2000 sp4
script with 33000 commands (about half of them inserts)

It will be a tremendous help for me if you tell me what I could have done wrong. Or possibly, give me the SCSI parameters used in your tests. If there are other issues (sql server, ntfs, windows etc) I will appreciate knowing about them too.

Thanks a million
Avi


The result of all the tests is as follows:
· With the current configuration IDE disk give much better performance than SCSI (40 seconds compared to 3.30 minutes)
· The faster the SCSI controller the slower the performance. In other words, writing to the disk on Motorola NMS was faster than the Dual CPU Xeon.
· SCSI 320 Ultra with RAID configuration gave good result (50 seconds).
· If all the SQL commands are done in one transaction also the fast SCSI (without RAID) finished in 30 seconds. However, this is not regular NMS behavior and was done just to better understand our problem.
· Changing the SCSI controller (and drivers) did not improve performance.
· Benchmark utilities showed that the SCSI reads and writes are much faster than their IDE counterpart.
· Both Sybase and SQL Server gave poor performance with SCSI

 

[EdtheROM]

A couple of things...

I find that something in W2K.sp4 slows my raid system [adaptec 3400s w/ 128mb ecc cache]. I now use sp3 with selected postsp patches

Exactly what controller are you using? Model number?? specs?

Are you using the very latest drivers?

What is the configuration and what options are enabled on the board ?

Is it in a 32bit/33mhz, 32/66 or a 64/66 slot?

I assume you are using Raid5.
What is the stripe size?
How many drives?
How many channels?
How are the drives distributed over the channels vailable ??

Have you enabled things as command queing which won't show up near as much in some benchmarks as a real assault on the array from something like a continous accessing of SQL
SOmetimes these things have to initially be enabled in the setup at boot.

It would help [me, at least] th have the transfer rate in MB/sec. I can't relate to the proportional data.

Finally, are the drives set up correctly? Sometimes the options set by default may not be the best for your situation

It sounds like it *could* be choking. Scsi can choke with the new high-speed [u320] drives rather easily if there are several and they are on a 32/33 slot and 1 [or even 2] channels, severely limiting transfer rates. Maybe something else? is limiting the transfer rate??

Just some thoughts, no guarantee, but you might check


.
.
-=ed

 

[blah]

you know so much, that don't even read tho post properly. the guy says that SCSI "reads and writes are much faster" than the IDE, any more questions?


..this is very useful and helpful place for information...

 

[blah]

Well, from the stuff that you were willing to provide here, even I might make a horrabelle mistake, I can see that you are using SCSI card with software RAID setup, and not a SCSI RAID controller. That is where your speed drop is, especially with w2k. If money is not an issue here, get the good hardware RAID controller ($1000+); you will see what SCSI can do. Make sure you have the same drives across the RAID (-5?) (the more drives the better speed, even you will not notice it a lot, parity drive makes a hit), they MUST have an active cooling all the time. I understand you will have to rebuild the system from the scratch with the hardware controller, but it is worth it a lot. Take care.

..this is very useful and helpful place for information...

 

[EdtheROM]

how much faster.

What IS the ide speed. I see different speeds depending on the drive, the motherboard, the operating mode.

That way I can tell HOW MUCH slower it is, and maybe figure out why.

20% slower would indicate a different possibility than 50% or 70% slower

BTW, software raid with only 2 or 3 drives, with a suitably fast cpu and buss is as fast, and sometimes faster than a hardware controller.. hardware pushes ahead with many drives and a heavy i/o load.

Also, you need to learn to read... and write.

Any more questions??

.
.
-=ed<P ID="edit"><FONT SIZE=-1><EM>Edited by EdtheROM on 12/18/03 04:48 PM.</EM></FONT></P>

 

[blah]

I know how to read and somehow to write as well, even your language is my third one I am using at this point. Try that if you could, any more questions?

And he was talking about 300 to 400 percent difference, there is something wrong with the picture.

PS: spellchecker was not used, grammar was not checked, written by foreigner, don't be stupid complaining, any more questions, talk to my secretary.

..this is very useful and helpful place for information...

 

[blah]

heck, as little language as I have, I was working for one of the top insurance companies in Seattle, was programming at MS (even not proud of it now have my own company and still getting calls for contracts, can you do that with your LOTS of knowlage in English spellchecker?

..this is very useful and helpful place for information...

 

[EdtheROM]

I nevr use a spellchecker. One of my degrees is in creative writing.

Too bad I can't understand you, and you can't understand English

I have no respect for insurance, and the programmers at M$ can't even check a buffer, a bush league hack.


.
.
-=ed

 

[EdtheROM]

I know English, I don't have to know any other language. I do speak a little french, which I never use anymore.

I can tell you didn't use a spell checker, and you don't know much grammer. Your statements are ambiguous, and open to interpretation.

I didn't complain, I inquired. I was a little confused by his post.

So you don't know English as well as you think. A little knowledge is just that... little

I was troubleshooting in my own way, and you came out of the blue and attacked me.

Concerning your "secretary", can she speak better English than you write?? Otherwise, I won't understand her, either.


sincerely:



.
.
-=ed

 

[blah]

<""I nevr use a spellchecker"">

I can see that.

<""Too bad I can't understand you, and you can't understand English"">

You are right, you can't understand me, caz you r just one of natcies who think they are belly button of the world, and you are wrong, I understand Englaash vareee well. It is primitive language, there is nothing to understand.. (explaination follows)



..this is very useful and helpful place for information...

 

[blah]

<""Concerning your "secretary", can she speak better English than you write??"">

this is why it is a primitive language, you don't even know who my secretary is - she or he, that is primitive in my language, same goes for a friend, partner, etc. That is why you have such tough time understanding people, caz you grow up in primitive culture.

..this is very useful and helpful place for information...

 

[aviw]

Hi,
Thanks for the reply.

Below is my machine’s configuration. As you can see it is a pretty fast machine. I did not have it RAID configured. The ironic part is that when I did, it gave better response (50 seconds compared to 3 minutes)

I had the “Cache write on” on both my disks, but it not have any affect.

Thanks,


Type Intel@ Xeon 19” Rack Mounted 1U SR1300
CPU 2*INTEL XEON 1.8GH 512K L2 (Dual CPU)
ECash memory L2 Cash 512KB
Mother Board SE7501WV2 533MH WE DUAL XEON
RAM 2*512M DDR 133Mhz ECC Total 1G RAM
Video Card Onboard AGP 4MB MIN
Hard Drive 2*36GB 80P 10K Ultra320 HDD (also Ultra 160) SCSI
Slots 2 PCI Slots min
Operating System Microsoft Windows 2000 Server OEMFull Package -ENA
Network Dual Intel-Network on board Dual Port
Additional Network card Intel PRO/100+server adapter-OEM
Mouse INTELIMOUSE
CD-ROM Slimline CD (Slim Size)
Keyboard Microsoft (English Only) Kbd 107
Power Supply 275W MIN
Floppy 1.44M 3.5 (Slim Size)

 

[aviw]

I may have put my last post in the wrong place. Please refer to the second post. It is my reply to you.

Thanks

 

[blah]

as Adaptec says that thing can do 0 and 1 RAID, but that is what they say, in reality it is a complete soft based thing or HostRAID which is kind of WinModem functionality, nothing special, if you need speed, get a real RAID card. Otherwise that's all you can get from that thing.

..this is very useful and helpful place for information...