Monday, March 29, 2010

Often developers just toss SQL Server on any handy drive and forget about it. Later when they are asked to provide guidance on installing SQL Server they provide zero often, or slip into pro-forma “mom’s apple pie” statements.

Today, I decided to do a little homework and downloaded SQLIO tool from Microsoft to get some numbers on my drives. I have two RAID-0 logical drives, one RAID-1 logical drive, 1 SSD Drive and an external SATA drive. In general, SATA drives are developer grade and definitely not production grade – production grade is SCSI drives.

So running just one test (using random access writes) on the drives, I got the following interesting table. I opted for random writes because it tends to bring out the nasty.

Drive

Latency Average

Latency Max

Ios/Sec

MBs/Sec

SSD (Intel)

32

414

966

121

Mirror @ 5200 RPM

207

515

153

20

Strip @ 7200 RPM

57

649

552

69

Strip @ 7200 RPM

63

841

500

62

eSata @ variable RPM

69

816

457

57

The RAID controller was the same for all drives.

The thing that amazed me was the sluggishness of the Mirrored Drive (yes, I checked, it is 0% fragmented – so that is not it). I expected it to be better then the eSata drive, instead it’s 1/3 the write rate, 3x the latency.

The mirrored drives happened to be the drives that came with the machine… which actually suggests that if you want performance, get your own drives!

The stripped drives performed 15% better than the eSata, so stripping paid off – but not as much as I would have expected.

Now the SSD did double the performance of the hard drives for writes, which is what I was expecting – doing writes is much slower for SSD then reads where 3 to 4x improvement is seen.

Drive

Model

Cache

SSD (Intel)

INTEL SSDSA2M160G2GC

Mirror @ 5200 RPM

WDC WD10EARS-00Y5B1

32 MB

Strip @ 7200 RPM

WDC WD100FAEX099Z3A0

64 MB

Strip @ 7200 RPM

WDC WD100FAEX099Z3A0

64 MB

eSata @ 5-7200 RPM

ST315005N4A1AS

32 MB

For reference, I did some read tests:

Drive

Latency Average

Latency Max

Ios/Sec

MBs/Sec

SSD (Intel)

12

1147

2384

298

Strip @ 7200 RPM

44

1372

710

89

Mirror @ 5200 ROM

101

1326

314

39

eSata @ 5-7200 RPM

74

1489

426

53

Now the SSD is on a 300 MB/sec SATA bus … so 298 is about as good as it can get! The read time for Stripped is twice that of Mirrored – which follows theory, but not quite twice of the eSata.

So what do these numbers means? Well, forget putting TempDB etc on my mirrored drives. One of the stripped drives will be for TempDb and the other one for TempDb log.

There are more experiments that I will likely do, using 4 drives for stripping instead of 2. Trying RAID 5 (which I expect to be worst than Mirror), etc. None of these conclusions can be safely generalized. You have to do the grunt work of testing each drive/configuration – and expect some surprises for technology has gotten so complex, nothing is obvious any more.