Monday, March 29, 2010

Configuring any SQL Server – the forgotten homework…

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
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.


Remember, be a fact-based developer!

No comments:

Post a Comment