Optimal placement of tempdb, mdf and ldf files in SQL Server 2012 on SSD's? I realize this is probably a very open ended question and the answers could vary, but what is the optimal placement for the tempdb, mdf and ldf files in SQL Server 2012 when talking SSD's?

Pre-new-purchase, I had an existing SSD with the SQL Server 2012 core files and tempdb installed on and had both the mdf/ldf on a 7200rpm HDD. I then bought 2 SSD's with the original intention of putting mdf on one and ldf on the other.

But, from reading into it more, separate physical disks for mdf and ldf files don't really apply when it comes to SSD's. Correct?

So, I was thinking of the following:

SSD 1 - SQL Server 2012 Core Files and Windows
SSD 2 - tempdb
SSD 3 - mdf and ldf

If it makes a difference, this will be dedicated to just one database so there won't be any contention between multiple databases.

Is my "thinking" setup good or just simply a waste (i.e. no reason to separate out tempdb) where I now have an extra SSD to make use of elsewhere?
But, from reading into it more, separate physical disks for mdf and ldf files don't really apply when it comes to SSD's. Correct?


The original reason for splitting log and data files off onto seperate disks was 2 fold - latency and bandwidth on the drives.

SSDs don't remove these restrictions, but they do decrease/increase the limits quite significantly (7.9ms for a read with a single HDD vs 0.1ms for a read in a single SSD, roughly).

So ultimately yes and no - it doesn't apply AS MUCH as with HDDs, but those limits are still there and can still be met.  It all depends on your workload.


  Is my "thinking" setup good or just simply a waste (i.e. no reason to separate out tempdb) where I now have an extra SSD to make use of elsewhere?


Assuming that


You have 3 physical SSD's
You have 1 physical HDD
You need the data to be redundant, but not necessarily the system itself


Your proposed setup would have a few issues (as mentioned before), and a single drive failing is the main one.

You could go for something like this.

Single 7200rpm drive - Windows OS
RAID 5 array (3 SSDs) - Broken down into 4 drives (D for Data, L for Logs, S for Swap and T for Temp)

OR

Single 7200rpm drive - Windows OS
Single SSD - Temp and Swap
RAID 1 array (2 SSDs) - Data and Logs

It's personal preference of mine offloading Windows onto a non-SSD drive when you only have a limited number, but this entirely depends on what the server is doing and how much of a risk you're willing to take.