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.
asked Dec 14, 2009 at 09:09 AM in Default
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.
answered Dec 14, 2009 at 01:18 PM
Matt Whitfield ♦♦
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:
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.
answered Dec 14, 2009 at 10:17 AM
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. ;-)
answered May 11, 2010 at 12:44 AM
An average transaction is 40 bytes only?
answered Dec 14, 2009 at 09:41 AM
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?
answered May 10, 2010 at 11:16 PM