1

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.

Improve this question
0

3 Answers 3

Reset to default
3

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.
Improve this answer
5
  • I've added more detail, see above.
    – Jeff Siver
    Oct 18, 2012 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, 2012 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, 2012 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, 2012 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, 2012 at 15:58
2

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

Improve this answer
0

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.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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