SSDs or Spinning Disks—What’s Right for Me?

I'll skip over the typically answer here of "it depends" and move towards something a little more useful.  Solid State Disks (SSDs) also called Enterprise Flash Disks (EFDs) by EMC are great for some workloads, specifically random workloads. Typically. when you talk about random workloads you are talking about a traditional OLTP database. The reason that OLTP database workloads are so random is that when working with the data within the database most of the data is already in memory. We only need to get the occasional block from disk, which after being retrieved from disk is then stored in the SQL Server's buffer pool from a long time (hopefully).

Related: Me and My New Crucial M4 SSD

The next time that we need that block of data it’s already stored in the SQL Server’s buffer pool, so there’s no need to request it from disk again. The next time that we need to get data from the disk the new block is going to be in a totally different part of the disk, making our workload totally random. These random workloads are where the SSDs really shine because they don't have any moving parts to spin from one location to another, unlike traditional hard drives.

Before spending the money to put SSDs into your database you really need to look at the system overall to make sure that SSDs are going to be worth the money. If you have a system with a 100 Gig database, and 8 Gigs of RAM (which is a configuration that I've seen many times) that is having IO performance problems you could look at putting SSDs into the system to make the IO performance problems go away. However, before you do, you might want to look at increasing the RAM requirements. Odds are that your SQL Server isn't keeping data in memory for very long, and needs to pull the same data off of disk over and over again (look at the Page Life Expectancy performance monitor counter to see how long data stays in memory) which is causing the storage system to work much harder than it needs to in order to service all the extra requests which shouldn't be there. In this case before switching to SSDs, add more RAM to the system. RAM is much cheaper than SSDs would be, and will more than likely solve your storage performance problem.

Related: Will SSDs Cause Performance Tuning Experts to Go the Way of the Milkman?

Now I do understand the desire to have SSDs in your server. Being able to tell people that you have SSDs at work makes you the cool kid in the SQL Server community. But all too often I see companies spending huge amounts of money on SSDs when more traditional techniques could be used to solve the problems. When you are solving the problem by using SSDs and not other techniques like adding more RAM, fixing indexes, etc. you aren’t actually solving the problem. All you are doing is simply masking the problem with a really fast hard drive. Eventually the problem will come back to bite you, and when it does it’s going to bite you really hard and it's going to hurt, a lot.