The replacement of HDD with SSD.

Question

question

sp_lock asked Dec 14, '09

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.

http://h20195.www2.hp.com/v2/GetPDF.aspx/4AA2-6615EEW.pdf

sql-server-2008 storage ssd

3

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Håkan Winther commented · Dec 15, 2009 at 05:22 AM

Excellent question! I am interested in how it turn out for you, and hope you will share any experience on this community.

0 ·

sp_lock commented · Dec 15, 2009 at 06:01 AM

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!

0 ·

Mark Allison commented · Jan 07, 2010 at 06:13 AM

+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! :-)

0 ·

Answer 1 · 2009-12-14T10:17:39Z

TimothyAWiseman answered Dec 14, '09

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:

Is the improvement sufficient to justify the cost?
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.

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Answer 2 · 2009-12-14T13:18:57Z

Matt Whitfield answered Dec 14, '09

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.

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Answer 3 · 2009-12-14T09:41:47Z

Peso answered Dec 14, '09

An average transaction is 40 bytes only?

3

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Kev Riley ♦♦ commented · Dec 14, 2009 at 12:59 PM

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.

1 ·

sp_lock commented · Dec 14, 2009 at 10:33 AM

Not sure I understand your question Peso..?

0 ·

sp_lock commented · Dec 14, 2009 at 01:16 PM

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.

0 ·

Answer 4 · 2010-01-06T15:21:06Z

Bob Leithiser answered Jan 6, '10

Not sure why you would want to bother with a SSD for an average of 100 transactions per minute. A single SAS 15K hard drive already should give you close to 150 IOPs. With the database size less than 20GB, more memory would also be more economical than a SSD to ensure that the entire database including the indexes are cached.

2

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Peso commented · Jan 06, 2010 at 04:57 PM

With the cheap price of RAM memory these days, the complete database can be run on a RAM drive.

0 ·

Mark Allison commented · Jan 07, 2010 at 06:17 AM

Indeed the OP needs to be looking at physical I/Os very closely. If the page life expectancy is high and the database is primarily read, then most I/Os will be logical and read from the buffer cache, and therefore you would not see the benefit of the SSD read performance at all.

0 ·

Answer 5 · 2010-05-10T23:16:45Z

MoxyDave answered May 10, '10

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

http://blogs.msdn.com/e7/archive/2009/05/05/support-and-q-a-for-solid-state-drives-and.aspx

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?

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Answer 6 · 2010-05-11T00:44:47Z

Jeff Moden answered May 11, '10

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

1

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

TimothyAWiseman commented · May 11, 2010 at 02:00 AM

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

0 ·

question