SQL VM 10k or 15k Drive recommendation?


(Lee Ostlind) #1

Any thoughts about SQL on a VM and what drive speed, 10k or 15k, works best? I know faster is better, but will the 10k drives provide good performance in a RAID 5 for this? The individual 15k drive capacity doesn’t offer the data footprint needed when in a RAID 5. Thanks!


(Christopher Harvey) #2

I assume you’ll dedicate a new array just to that SQL VM? (instead of SAN for use by many VMs) I’m also assuming it’s a pretty large database from your comment about capacity, so I expect most will say that you should go 15k. I won’t disagree with that; but if you need to go with 10k drives for cost or capacity; the other way you could get some speed back is with a single dedicated SSD drive for temp.db files. That assumes the SSD is local to the host which will run the VM or that bandwidth to the SAN is not a bottleneck.


(Alex Conner) #3

What is the planned workload? Of course 15K drives are faster than 10K, but if you’re looking at handling 10 transactions/second it won’t matter. And if you’re looking to do 1000 you’re nowhere near the right direction.

Also, if you’re doing a SAN, then I/O latency is generally the indicator you want to watch. Spin it up with what you’ve got and benchmark the app. See where it is now, and then you can decide where to go. But unless you’re going to have very low write traffic, RAID5 isn’t going to give you what you want.

Let’s do some simple math. Say you have an insanely huge 2TB database and 6 bays to work with. As a rule of thumb, these are the IOPS figures to work from.

So, 6x 1000 GB 7.2k SATA drives in a RAID10 gives you 3TB Usable and 225 write IOPs and 450 read IOPs
6x600 GB 10K SAS drives in a RAID5 gives you 3TB usable and 140 write IOPs and 625 read IOPs
6x600 GB 15K SAS drives in the same setup gives you 175 write IOPs and 875 read IOPs

If you’re running transaction logs and DBs and tempdb on the same spindles, expect peak TPS numbers to be about 1/4 of the IOPs numbers. Even less if you’re sharing the spindles with Windows since it likes to burn dozens of IOPs for no reason at idle. Also, I find optimizing disks for reads on databases is a bit silly since you’ll likely be doing most of that from memory and not even hitting the disks but every write has to wait for the disks.