5

I just learned from an answer to this question that the .mdf and .ldf files that SQL Server uses are an important pair for the database. I previously thought the .ldf was just a non-critical log for backups or audits.

Anyway, our server right now has the .mdf on a SSD and the .ldf on the main HDD with Windows. I set it up like that because I assumed the .mdf was the most performance critical file location.

But now it seems like the .ldf is actually the first place data is written in the event of an UPDATE, and then the .mdf is written to later on.

Should I put both files on the SSD? Or is it better if they're on different drives, even if one of them is an HDD? (and if so, which file should go where?)

If it makes a difference, SELECT performance is more important to me (and more common) than UPDATE.

Improve this question

1 Answer 1

Reset to default
5

These are the general rules for the data (mdf) and log (ldf) files:

  1. Tune your mdf files for random read/write access in 64 KB chunks.
  2. Tune your ldf files for sequential write access in 64 KB chunks.

Because the mdf files are accessed randomly, and the log files accessed sequentially, you can see substantial write performance drops if the logs are sharing disks with other data that's frequently accessed. This is largely because of the time lost waiting on the heads to seek around the disk. Obviously with an SSD you don't have this seek penalty, but the disk will still have a maximum write throughput to consider.

In a data warehouse/OLAP type of server, this may be less of an issue, as most day-to-day use will probably be read-only, and ETL batches will probably run during non-peak hours. The data files and log files likely won't compete with each other as much.

So, the moral of the story is if you're not happy with the write performance of your database, I'd recommend moving the logs to a dedicated volume with higher sequential write throughput. Often, this will mean using a RAID10 volume with traditional spinning disks (not SSDs). The more disks you use, the faster it will be. Don't use RAID5 for logs, as the write performance is generally quite bad, unless your array controller has a large enough cache to mask the effect.

Improve this answer
4
  • This is a good detailed answer, but to be more specific to this question's case, I would say move both the MDF and LDF to the SSD. Agree @db2?
    – RoKa
    Apr 18, 2013 at 13:19
  • 2
    @RoKa Tough to say really, without testing that specific machine. If the SSDs are near peak already, then the logs might actually have higher latency on that volume. But if the server is filled with enough RAM to keep data file I/O to a minimum, then it might be a good choice.
    – db2
    Apr 18, 2013 at 13:53
  • 1
    Performance characteristics put aside, it might be a good idea to put logs on HDDs as long as the long-term reliability of heavily-written SSDs is still unclear. HDDs have served well as log devices for decades now, their deficiencies are well understood and can be planned for.
    – syneticon-dj
    Apr 22, 2013 at 8:52
  • @syneticon-dj Very true, and the stream write performance of conventional hard drives will often be better, from what I understand.
    – db2
    Apr 22, 2013 at 13:03

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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