Take the 2-minute tour ×
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.

I have a SQL Server and I put the data files (the .mdf) on a different partition.

Windows runs from C: and the data files and logs are on D:

Is it advantageous or disadvantageous to separate the data files on different partitions?

There is only one disk and it has been partitioned to C: and D: My main focus is SQL Server performance all around like reading and writing.

share|improve this question

migrated from stackoverflow.com Oct 18 '12 at 15:49

This question came from our site for professional and enthusiast programmers.

3 Answers 3

up vote 3 down vote accepted

It won't matter for performance but it could matter for other reasons.

To get better performance, you need to have it split across different physical hardware; splitting files across logical partitions does not help or hurt performance. But I wouldn't worry about this unless you start seeing disk activity as a bottleneck.

There are benefits from this approach:

  1. You can back up the D: drive without having to do a system image backup.
  2. You can restore the D: drive without restoring the entire system.
  3. If you get to a point where you need more space, it is easier to move the D: drive to another physical disk than to have to move the C: drive or relocate the database files.
share|improve this answer
    
I've added more detail, see above. –  Jeff Siver Oct 18 '12 at 13:45
    
Because it has been partitioned, would it take more time for the disk seeker to run between the two partitions because it also has to run Windows also? If everything was in one location, the data will be closer to each other? –  iefpw Oct 18 '12 at 13:49
    
Honestly, in my experience that doesn't matter. No matter what, the data is not contiguous. The other problem is that disk is way slower than memory. So if disk access runs at 1/100th the speed of memory, improving disk access by 1% means it is now running at 1/99th the speed; any improvement is too small to see. Plus, based upon the way disks are managed, that improvement is fleeting. –  Jeff Siver Oct 18 '12 at 13:52
    
If you really want to see performance improvements, add more memory and tune your queries. Both of those will have a bigger performance impact than how the disks are configured. –  Jeff Siver Oct 18 '12 at 13:55
    
If you had multiple physical disks I would suggest Splitting your TempDB from your actual Database but seeing how it's partitioned you will probably see no real benefit. –  Zane Oct 18 '12 at 15:58

you will only have performance benefits if you have your database files on different phisical disks. Partitioning is a logical separation only, in the end the files will be fighting for the same I/O resources.

FYI: before putting 2 partitions on separate disks, you should consider putting the tempDB and the log file on separate disks because these 2 will for sure have more access than any other database file

share|improve this answer

The only benefit I could see from partitioning a physical disk or array into multiple drive letters would be: -Ease of administration & manageability (let's say you have automated scripts and it always looks for a specific drive letter)

-PerfMon counters. In some cases, although it's on the same physical disk, you can still get more disk details such as access patterns if you change the drive letter.

You probably know, but just in case, you are not fully ACID compliant by putting the TLOG and data files on the same physical drive/array. When it get's corrupted or destroyed, you will not be able to backup the tail of the log thus you will lose that last bit of data.

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.