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 |
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.
Remember, be a fact-based developer!
Comments
Post a Comment