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