The replacement of HDD with SSD.

I am working on a new project that is limiting me to the number of physical disks that can be used for SQL Server.

I have read a number of posts with regards to the IOPS and read/write rate for HHDs vs SSDS.

Would replacing 12 HDDs with 4 SSDs be benificial? I know in most cases it will be "it depends", but I really looking for a guiding light on this one (SSD link below)

Currently I have a 1u server that will allow upto 10 HDD's. The plan was to have 2 disks for the OS, 4 (raid 10) for SQL data, 4 for SQL Logs and 2 for SQL TempDB.

This is intially for a small database (20Gb, growing about 2gb per year) with on average 100 trans per minute. This SSDS would be setup as Radi 10.


more ▼

asked Dec 14, 2009 at 09:09 AM in Default

sp_lock gravatar image

9.3k 25 28 31

Excellent question! I am interested in how it turn out for you, and hope you will share any experience on this community.
Dec 15, 2009 at 05:22 AM Håkan Winther

Once the h/w is installed I will run some IO test and feedback to SSC.

Thanks all for your posts! As always it is much appreciated!
Dec 15, 2009 at 06:01 AM sp_lock
+1 for a great question. I would be interested on how they compare to SAN storage too - please post your I/O test results. Combine this with data compression, and you could have one screaming SQL Server! :-)
Jan 07, 2010 at 06:13 AM Mark Allison
(comments are locked)
10|1200 characters needed characters left

6 answers: sort voted first

Based on your growth numbers, it seems to me that you will be doing a lot more reading from this DB than writing to it, which is an environment that suits SSDs well.

However, a word of warning: that link to the document you posted states that the expected life of the drive is only expected to last for the life of the server under 'constrained workloads'. Effectively, an SSD will only wear out on write cycles, but it still irks me that the HP data sheet says 'constrained workloads'. Also, it's worth asking yourself why they have downed the warranty on their 'Gen2' SSDs to 1 year...

Another thing to watch with SSDs is the controller - which you're not going to be able to tell from the HP datasheet. Rough rule of thumb - Intel/Indilinx = Good, JMicron = Not so good.

One other word of warning: SSDs slow down over time. This is due to wear-levelling. If your SSD has TRIM (basically garbage collection for SSDs) support, then that can help, but it's still a factor. The HP sheet makes no mention of TRIM afaics.

The bottom line? They're probably not ready for the 'prime time' in terms of enterprise class reliability - but, if you have understood and mitigated the risks, then they can really boost performance in random read-intensive scenarios.

more ▼

answered Dec 14, 2009 at 01:18 PM

Matt Whitfield gravatar image

Matt Whitfield ♦♦
29.5k 61 65 87

Matt, thanks for your answer... We are also looking at the Intel X25-e which is similar to the HP but £300 cheaper.

I see what you are saying on the reliability side and will take this into consideration.. Its just with the constraints of rack space and the amount of disks possible to configure and reliable SQL solution I seem to fighting a all too commmon problem

Buy... Configure... Setup.... Go Live.... YESTERDAY!
Dec 14, 2009 at 02:30 PM sp_lock
The X25-E drives are certainly good - their performance is up there with the best of breed... Good luck & let us know how it goes :)
Dec 15, 2009 at 08:01 AM Matt Whitfield ♦♦
(comments are locked)
10|1200 characters needed characters left

I have not yet personally evaluated SSDs, but from everything I have read they have overall faster read and write speeds and the read speeds are vastly faster. Based on that alone, switching to SSDs would be beneficial for performance.

That is probably not the interesting question though. The two interesting questions are:

  1. Is the improvement sufficient to justify the cost?

  2. Given that you said you are limited on the number of physical drives you can use, and that it is currently very hard to get very large SSDs, can you get enough space from SSD drives to fit your needs?

The answer to both of those are very much, "it depends" but as a general rule, the answer to number 1 at least will be no. I would currently only seriously consider an SSD at their price point for a database if performance was truly vital and I had already taken every other practical step (including maxing out the amount of RAM in the system) to improve performance first. That is likely to change quickly in the near future as the price point drops, but currently the price difference is enormous, especially if you need to store a large amount of data.

One "in between" or compromise option to look at if performance is truly vital is to use SSDs only for the indexes and store everything else on conventional hard drives. Again, I have not tested it personally but I have heard anecdotal stories of good results that way.

more ▼

answered Dec 14, 2009 at 10:17 AM

TimothyAWiseman gravatar image

15.6k 21 23 32

Thanks for the respose.

The SSD in my article above is around £500 for a 60gb device. With that in mind it would be sufficient space for the db over the next 18-24 month (easily). The high end SAS drives are £400.

The RAM of the server will be 16Gb on a x64 system (both windows and sql 2008 ent).

I a tempted to look @ the 160Gb intel x25 (times 4) to service the whole data, log and tempdb (240Mb write - 130Mb read). Would you advise this even with SSDs?
Dec 14, 2009 at 10:37 AM sp_lock

With those additional details, if speed is very important to you it might be worth using SSDs. But that is very much dependent on the databases not growing faster than you expect. I have often seen even the best estimates be very wrong as data usage suddenly spikes.

Also, be sure to read Matt's answer. He makes some very good additional points. One in particular is that this is still relatively new technology and many do not consider it ready for enterprise use.
Dec 14, 2009 at 02:07 PM TimothyAWiseman
(comments are locked)
10|1200 characters needed characters left

You could get much better performance if you saved the money that you would have spent on the new SSDs by sending your Developers to a decent programming school so they can write more effecient code faster and send the Managers/PM's to school to learn how to properly estimate project duration and resources. ;-)

more ▼

answered May 11, 2010 at 12:44 AM

Jeff Moden gravatar image

Jeff Moden
1.8k 3 4 8

True and good point. Of course, if you have the money, do both (after maxing out the RAM, which is generally the best price/performance ratio you will find with SQL Server in most instances).
May 11, 2010 at 02:00 AM TimothyAWiseman
(comments are locked)
10|1200 characters needed characters left

An average transaction is 40 bytes only?

more ▼

answered Dec 14, 2009 at 09:41 AM

Peso gravatar image

1.6k 5 6 8

Not sure I understand your question Peso..?
Dec 14, 2009 at 10:33 AM sp_lock
Peso is suggesting that 100 trans per min = 52,560,000 per year, and if you grow by 2Gb per year (2,147,483,648 bytes) then that equates to 40 bytes per transaction.
Dec 14, 2009 at 12:59 PM Kev Riley ♦♦

Now i see... Yeah we have a archieve routine that moves records older that 3 months to a different server. Therefore the database only grows as we increase our volumes.

Database only contains 10 tables to handle call data from a telephoney system but only 2 table are handling the tranactional data.
Dec 14, 2009 at 01:16 PM sp_lock
(comments are locked)
10|1200 characters needed characters left

Bump since what the heck, I am researching SSDs right now:


I would use an SSD for the system pagefile, attached to a built-in SATA connection if possible (most RAID controllers do not support TRIM). Then a RAID 1 SSD for any database temp files. I don't know much about SQL but I believe this would improve performance.

You probably finished the project already, how did it turn out?

more ▼

answered May 10, 2010 at 11:16 PM

MoxyDave gravatar image


(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Dec 14, 2009 at 09:09 AM

Seen: 5544 times

Last Updated: Dec 14, 2009 at 09:09 AM