Configuring SQL for optimal performance... SSD or HDD?

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.