Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 5 down vote favorite

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?

share|improve this question
2  
Is fault tolerance an option with your setup? If your database is important at all, the mdf and ldf drives should be stored on separate fault-tolerant (e.g. mirrored) drives. – datagod Sep 14 '12 at 15:09
3  
One potential issue I see with your setup is that you have not accounted for a single drive failure. If you only have the three SSDs to work with, I would recommend considering a RAID5 array, and placing all of your SQL Server related files on that array. – Matt M Sep 14 '12 at 15:10
2  
Is this a production tier server, or do you care if it goes down if a drive fails? – Jon Seigel Sep 14 '12 at 16:32
1  
Forgot to mention that piece--fault tolerance isn't a concern since the data is backed up in full, nightly, but it's mostly static data that I can easily replace even if backups weren't an option. The only dynamic data is mostly logging/auditing which is has no dependencies. Any small interruption in service I would have in cutting over to a backup, manually, is tolerable. – Kevin Sep 15 '12 at 6:11
I appreciate the responses, all. I do have a follow-up question regarding "is it best to format the ssd to 64k blocks?", but I'm not familiar with the format here. Should I post that as a new question or is this ok here? – Kevin Sep 15 '12 at 6:22

1 Answer

up vote 1 down vote accepted

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.