Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

I'm looking to buy an SSD for a SQL Server computer, what benchmarks should I be comparing? High/low queue depth? Random/sequential transfer rates? IOPS?

share|improve this question
add comment

3 Answers

IOPS pretty much.

Queue depth is irrelevant - basiaclly some higher end systems work better with more stuff queued. This wont give you any sensible information without context.

Transfer rates depend on IOPS - someone has to read / write the stuff first.

At the end, random IO, always runs down to IOPS. IOPS directy transfer to transfer rates when needed. Note that SQL Server ALWAYS does 64k IOs or multiple of that for enterprise. So, 4kb etc. is not relevant. From that you can pull all other information. Response time too slow, queues up - not enough IOPS available.

share|improve this answer
    
True; small (<<64K) IOPS generally heavily depend on queue depth. Setting your queue depth=4 on any given block device is not going to get you any performance. The queue depth (driver, hba, OS dependant etc.) defaults should at least yield something usable. For tuning I'd always suggest looking at queue depth. But as said, with up to 64KB (32 pages) of IO size the depth is not that relevant any more. –  pfo Apr 13 '12 at 12:50
    
But SQL Server does not do small reads either. All IO Is in 64kb increments - an extend of 8 pages of 8kb. Plus - queue depth is ONLY relevant in context. I once knew a SAN where they ran 24 discs because each disc go t a 200+ queue length - but that was only sensible on that hardware. EXTREMELY hard to properly analyze. IOPS budget numbers make sense without context. –  TomTom Apr 13 '12 at 13:19
    
That is not true, since IOPS Numbers are for a given queue depth and maximal latency - usually say < 20ms. –  pfo Apr 13 '12 at 13:27
    
Something doesn't make sense to me: Queue depth is a property of the benchmark, not of the result. That is, drive A can sustain 40K IOPS at Queue depth 1 and 100K IOPS at queue depth 16 vs drive B which sustains 20K/150K at depths 1 and 16. What number should I compare? –  ytoledano Apr 14 '12 at 7:45
    
In general queue as much as you can - sql server will do the same anyway. It is quite good at that. Mostly yo uget as much IOPS as you can - the rest is not related to drive speed. For SSD expect queue length to be small ;) That stuff is just brutally fast. –  TomTom Apr 14 '12 at 8:15
add comment

The benchmarks you want are IO latency for the following workloads:

  • Random reads
  • Random writes

You might also be interested in the throughput (MB/s) of these workloads:

  • Sequential reads
  • Sequential writes

For all the writes, bear in mind that caching to volatile memory before IO hits the disk can make MB/s seem higher than it really is. SSDs are as good at random IO as they are at sequential IO.

share|improve this answer
add comment

The short answer is "It Depends"

Unfortunately without knowing your usage patterns it is almost impossible to suggest an "ideal" solution.

Some things to keep in mind

  1. Separate your data, logs and O/S. You can put your data and O/S on a spinning disk and put only the logs on your SSD, this will get you a boost at a lower cost. You should really do this anyway, even if you are using spinning disks, separate all the portions out onto their own volumes, RAID 10 each if you can afford it.

  2. You may be better served by adding RAM to your solution, if you can load your entire database into RAM then it's so much faster than an SSD or HDD could ever be. You can accomplish this a few different ways.

  3. If you do go down the route of SSD throughout, make sure your backups are good. Best do this anyway :)

share|improve this answer
    
-1. He specifically asks for HDD / SSD relevant criteria. Assuming he has the rest nailed but problems comparing storage options. –  TomTom Apr 13 '12 at 11:09
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.