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

avatar image

10.9k 27 37 37

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

avatar image

Matt Whitfield ♦♦
29.5k 62 66 88

(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

avatar image

15.6k 22 57 38

(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

avatar image

Jeff Moden
2.3k 3 7 13

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

avatar image

1.6k 5 6 9

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

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

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: 5903 times

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

Copyright 2018 Redgate Software. Privacy Policy