question

pits avatar image
pits asked

mdf and ldf placement

Hello Experts, What is the reason that all ldf and mdf or file groups are advised to be kept on different drives? rgds,
mdffile locationldf
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

By the way, with 89 questions on the site, you should have fair idea how it works by now. Please look back at all your old questions. If answers there were helpful, vote them up by clicking on the thumbs up next to the answer. If an answer solved your question, indicate that by clicking the check box next to the answer.
4 Likes 4 ·
Tim avatar image
Tim answered
MBPS and IOPS. The more spindles you have the more throughput and IO you have. Also MDF is random IO and transaction logs are sequential writes. Isolating these read and write patterns enables the heads on the disk to work more efficiently. I have a blog post about IOPS and throughput [here]( http://www.timradney.com/iops).
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Nuts. Came in while I was typing. That's what I get for going with a long-winded answer. By the way, the link doesn't seem to be there.
0 Likes 0 ·
Grant Fritchey avatar image
Grant Fritchey answered
Let's assume for the moment that you only have single drive and it consists of just one disk, no RAID, no SAN or any other multi-disk setup. When you save a row of information to your database, it has to write twice, once to the transaction log and once to the data. These don't occur at the same time, but there are lots of them occurring because you have more than one person on your database. So, if you only have that one disk, each write and each read has to wait for the others to complete. But if you have more than one disk and more than one controller, you'll get simultaneous reads and writes. Further, you may decide that you want to maximize throughput for the log so you set it up as RAID 10, but you want maximum protection of the data so you set it up as RAID 5. Storing the two separately allows you to do this. Then, when you really start to get into bigger databases, you can look at choosing separate storage for indexes, partitions, BLOB storage, etc. All to maximize throughput at the disk.
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

NeerajTripathi avatar image
NeerajTripathi answered
Placing mdf and ldf on separate drives improve performance as Grant Sir already explained and also it is helpful in disaster. For example: if we have all the files on same disk and somehow disk crashed or inaccessible then we will not be able to recover the database to certain point and loose the data that might be unacceptable for production database. Hence, it's better to avail the benefits of placing mdf and ldf on separate disk to get the higher throughput for read, write and helpful for backup operation and disaster recovery.
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.