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,
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.
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).
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.
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.
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.
16 People are following this question.
