8

Does anyone know of any comparisons that show how SSDs compare to HDDs for performance in a SQL environment?

I'm trying to understand what type of performance benefit might be gained by moving to SSD.

spoon16
  • 245
  • 2
  • 4
  • 9
  • 1
    I'm sure a copy of the SQL standard will perform equally well on SSDs as on spinning-platter HDDs. Or perhaps you're interested in a particular attempted implementation of the SQL standard? – womble Sep 27 '09 at 02:29

10 Answers10

14

If you're doing a large amount of small reads, SSDs are much faster. Here's one of the few comparisons floating around about database performance. Look at the bottom graph for the short answer.

For raw performance, SSDs offer many advantages, the main one being that the seek time is effectively 0, which means all the small HD hits a database does are handled much faster.

There are however some concerns with the current generation on write lifetime, since after so many writes a block isn't usable anymore. They can write quite a bit, I believe the intel's say round a petabyte of bytes for their 32GB drives before they start reaching dangerous levels of ware...this will only get better with time.

For a better understanding of why they perform so much better, read this article from Anandtech on SSDs. He goes into great detail of drives, what's good, what's not, and the ins and outs of how they work. At the top is also a link to a followup articles that covers the latest series of drives.

Nick Craver
  • 823
  • 7
  • 15
  • 2
    In terms of wearing out due to limited write cycles, good SSDs are nearing the safety of spinning disks these days due to a combination of greater write cycle capability of individual cells, write merging "tricks" to reduce write block cycles required, and wear levelling algorithms. Even for high IO load applications like a very active database a good SSD is not much more likely to ware out before routine replacement then a spinning disk. Just keep regular, regularly tested, backups like with any storage solution. – David Spillett Sep 27 '09 at 13:31
  • I agree, that's why I call it a concern not a show-stopper...a cheap disk has a real problem with this right now, and it's more of a firmware issue. Within 6 months I don't think wear level will even be brought up anymore, it's progressed very well, very quickly. – Nick Craver Sep 27 '09 at 13:47
  • 1
    Good answer. Just to add to it I would think that in most circumstances the log should remain on a normal hard drive as it is sequentially written to and hard drives are cheap. Just have the actual database on an SSD as that is where the random access is. – PeteT Jun 07 '11 at 01:15
  • @PeteT - it varies/depends a bit, remember that *one particular* log is sequential yes, but on a server hosting many databases (like Stack Exchange hosting many sites at a database per) the actual behavior is much closer to random access/writes than sequential, so it depends on what/how much you're running. – Nick Craver Jun 07 '11 at 10:12
  • One beautiful thing is that when a SSD reaches end of life and cannot be written to any longer, it is still readable. You can't say that about a spinning disk that breaks. – djangofan Dec 28 '11 at 18:48
4

You could install your Operating system and SQL software on a standard hard drive and then add an SSD to just hold your database files. This should limit the number of writes that the SSD drive experiences and also maximize the amount of space available for your data on the drive.

Aaron Havens
  • 149
  • 2
3

i recommend you to read the following paper Migrating Server Storage to SSDs: Analysis of Tradeoffs, its quite a nice read.

In my opinion there are not enough benefits from SDDs in the server area yet. Maybe in a few years they might be worth buying, bot for now HDD are a better choice.

Alan Featherston
  • 182
  • 7
2

Nick Craver's answer is good; I just want to add two caveats to SSDs that I think people should be aware of:

a) SSD's issues with write wear are not going away, they are fundamental to the flash cells used. SLC cells have much higher write endurance than MLC, so the OP should consider getting a SLC drive over MLC. Of course, SLC is also significantly more expensive.

b) Current drives cache data on the drive before writing it out. Thus there is a risk of data loss if the power is cut during a write operation. It's something you can work around, but the cache is there both for performance, and for reducing write amplification.

IMHO neither of the above are dealbreakers. I would be ready to deploy SSDs to production today, but with some planning first.

  1. If a tiny risk of data loss is unacceptable, then conventional SAS harddisks with data caching turned off may be a better choice.
  2. I think you should measure the amount of data written to the SSD drive in a normal day. Based on this and the manufacturers wear specs, calculate the expected lifetime of the SSD with your usage pattern. If the expected lifetime is lower than the servers planned lifetime, then set a preemptive replacement date for the SSD. Just like airplane parts, swap it out before it becomes likely to fail.
  • Airplane parts? Not the best analogy if you extend it to NASAs shuttles, which run on IBM AP101S computers with a whopping 1GB of RAM and no HDD. The need for predictability and reliability being above *all* other considerations. – CJM Sep 28 '09 at 21:40
1

Have a read of this article (fairly old - 2009):

Summary: replace 24 x 15k RPM SAS drives with 6 (yes six) SSDs and still get 35% more performance. This was with Intel X25Ms which are no longer top dog for SSDs.

For database people this is fantastic as you can have smaller faster servers using less power.

Quango
  • 229
  • 1
  • 4
  • 17
1

One thing to consider is to have the transatcion log on a HDD and your MDF on a SSD. Also life span will depend greatly on the application type. OLTP may burn though quickly where as reasonably static data should have no issues.

Justin Sims
  • 11
  • 1
1

My own experience was mixed here...

Testing on windows 7 with sql server 2008 express r2. Running on an i7 Desktop with a Sandy Bridge and 12G installed ram (DDR3 i think?). Excuse the setup being desktop, I was just after seeign how many records I can manage on the i7 platform before I build a server.

I first ran these tests on the installed 1.5TB 7200rpm drive to get base timings.

10k records with updating procs, optimized the tables to store the previosly related data in a flat table, added indexes until I got the timings down to a few seconds as a starting point, then I duplicated the records up to 1.2 million and got a timing of 0:3:37 for the same updates. 3 1/2 minutes isnt bad for this non-raid setup.

Duping the records up to 2.56 million got me a timing of 0:15:57 - almost a 5x increase. Likely due mostly to paging past the installed 12G memory.

Installed the SSD drive and moved the databases over, the timing actually increased to just over 20 minutes. I'm assuming this is because page files are per hard drive and there was none by default on the SSD drive as it was not installed as the OS drive (bluescreens galore when i tried that).

Added a pagefile to the SSD drive and reran the test, 0:5:52 -m so the pagefile seems to have done the trick but I'm not sure a pagefile is a good fit for an SSD drive for all the above reasons, they are heavily writtin to and may add undue wear and tear on the drive.

One caveat, I also enabled Smartboost on that drive and that may have also influenced the timings, will rerun without it.

My best sense of it is that its easier to add memory these days, and for the cost maybe a raid 0+1 of Hybrid disks would do nearly as good a job without the issues.

edit: disabled the paging file on the SSD and let smart boost do its thing, timing improved from 5:52 mins to 4:55 mins for 2.56 million records with a series of 3 updates each. Ill try the 8G ssd cache on the seagate 750G hybrid drive next. then if thats not bad i'll try them in raid 0+1.

last update on this since this is an old thread - but i wanted to put the results i got out there so someone can find them.

Moving the database to the Seagate 750G Hybrid with an 8G SSD cache I ran the test a few times so that the SSD cache can learn. It gives me 5:15 m:s timing for the same test, updating 2.56 million records - thats close enough to the SSD performance (4:55 m:s with Intel Smartboost) for me to consider cost.

At around $50 more ($239 vs. $189 right now) the hybrid provides over 6x the storage and nearly the same performance, without running any additional software for optimization. In a raid 0+1 I expect to get the timings much improved and this drive has a 5 year warranty, heres hoping i wont need it.

John Faulkner
  • 11
  • 2
1

Some thing to keep in mind.

If you are hitting the database enough that your reads are slowing down and you need SSDs, then you need to fix your indexes or look at adding more RAM to the server.

Most database servers, once fully tuned don't need SSDs to run well.

mrdenny
  • 27,174
  • 4
  • 41
  • 69
  • My reads aren't actually slowing down. We are trying to optimize a "first hit" query where it takes about 120-130ms the first time the query is executed and 80ms after that. These times exclude creating the query plans and reading the relevant statistics. We can see that the difference in time in our case is spent reading from disk, so trying to explore how I can make that first hit closer to the 80ms. – spoon16 Sep 28 '09 at 21:46
  • So even after the first run, it still takes 80ms? Is that mostly data reads, or lots of CPU (aggregation or sorting)? Still think there is plenty of room for "traditional" optimization before going to exotic storage technologies. – BradC Sep 28 '09 at 22:28
  • If you have to go to the disk on first run of the query, then SQL is kicking the data out of cache for some reason. You may first want to look at what your page life expectancy is. If it's low then more RAM is in order. What's your cache hit ratio before, during and after the query? If it's below 99.8% or so then indexing and more RAM are in order. Are you doing ANY scans? If so then more indexing may be in order. – mrdenny Sep 29 '09 at 00:20
  • On a write-heavy workload with write-through caching (i.e. data physically committed to disk before the call returns) you should get noticeably better write performance from a SSD, assuming your application really is I/O bound. Note that this is the preferred caching strategy for SQL Server, especially on SANs, and MS require SANs to guarantee this behaviour to get certification for use with SQL Server. – ConcernedOfTunbridgeWells May 17 '10 at 10:58
0

The Microsoft Research article concerns itself with cost per Gb rather than the performance gain. It does not actually fit and test the drives but uses a retro-casting algorithm based on log files from actual servers.

Some things that come to mind with SSD and SQL:

1/ If you neglect to add the right indexes, SSD will be much more forgiving as random seek times are so low.

2/ Costs are way down against when that study was done and for small web applications, say for running the back end of a phone app, not enterprise Exchange servers, the performance could save expense on hiring a consultant to tune SQL Server.

3/ A single SSD drive with shadow copy has surely got to be cheaper than a bunch of spindles in a RAID cabinet and the controller and connections. Not to mention the power and heating and rack space.

4/ Spindles are notorious for being the part that most commonly dies on a computer. SSD has no moving parts and an hour of business down time could cost the price of an SSD in one go.

5/ Wear is a problem but they have ways to manage it (involving scattering blocks) which are possible because randomly fragmented data won't slow an SSD. Also, a small database on a large disk will likely not wear out in time to buy a cheaper new one in the future.

6/ There's a trend towards non-relational databases and doing joins in the middle-tier. This could really change things: I/O to simple unindexed tables on SSD drives on shards without performance penalty and with a much simpler scaling proposition. Also saving on SQL Server licenses per shard.

7/ This is all theoretical. If anyone has real-world performance testing against spindles, I'd love to see.

Luke

Luke Puplett
  • 939
  • 3
  • 16
  • 24
0

Personally, I wouldn't use SSDs for the reasons already mentioned; they will gradually slow down before eventually failing. We don't yet really know when this will be - current estimates are just that - estimates. Remember when we all purchased those 'indestructable' CDs in the early/mid-80s? A few years later, we regarded term storage of data on CD as much folly as using floppy disks.

If you have got your hardware, OS and DB all configured correctly, then you won't need to gamble on SSDs.

In a few years, when the products have matured a bit, it will be a different scenario. But until then...

CJM
  • 730
  • 3
  • 12
  • 28