principlesqlMinor
.mdf vs .ldf Performance considerations
Viewed 0 times
mdfconsiderationsldfperformance
Problem
I just learned from an answer to this question that the
Anyway, our server right now has the
But now it seems like the
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,
.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.Solution
These are the general rules for the data (mdf) and log (ldf) files:
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.
- Tune your mdf files for random read/write access in 64 KB chunks.
- 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.
Context
StackExchange Database Administrators Q#40351, answer score: 5
Revisions (0)
No revisions yet.