Where to store SQL database transaction log files (LDF)? On seperate disks or not?

Today, I got next question from a consultant: Should I put the transaction log files of my SQL database on seperate drives? This is a valid question because in a lot of books you read this recommendation. This recommendation is correct if you understand in which situation you need to do this. In this blogpost I will expain in which situations you should store the log file on seperate disks.
First of all you need to realize that this recommendation is made from a performance perspective. The transaction log file of your SQL database is used to store recent database transactions and is used to ensure database integrity in the event of a system restore. Every change in your database (Update, Delete, Insert) will result in a write action in the transaction log. A Read action on your database will not store anything in the transaction log. New entries in the transaction log will ALWAYS be added at the END of the transaction log. This is one of the reasons to put the transaction log on a seperate set of disks. Because entries are added at the END (Sequential writes) of the transaction log, it is not needed to move the head of the disk. This will save a lot of time. However, this only applies if you have ONE log file on your disks. If you have 2 or more log files on the same set of disks, you can't write sequential because the disks need to write in multiple log files. The disk head need to move between all log files. This is like random writes in the MDF file.

Rule 1: Store Log file on a seperate disk if you have only 1 log file on these disks.

Database activitity of every applications will differ. For applications like, Exact Globe, Exact Synergy, you will see that 98% of the database activity is caused by Read actions. Only 2 % are Write actions. Now you need to ask your self, should I use at least 2 seperate disks (mirror) for the log file which are used only in 2 % of the database actions. I will explain with next example. Assume you have 10 disks to store all data. Disk 1 and Disk 2 are used as a mirror for your Operating System and the SQL Server installation. Disk 3 and disk 4 will be used as a mirror for the LOG file. The remaining 6 disks can be used for the MDF file. This means that 6 disks need to handle 98 % Reads + 2 % Writes . 2 Disks (log file) will store all database transactions as a results of the 2 % Writes. The overall performance of your SQL server will be much better if you store the LDF and the MDF file of the same set of disks. Overall read performance will increase with 33%. (8 disks instead of 6 disks) to read data. Beside this, SQL Server will write data in batches. This is handled by the Lazy writer process.

Rule 2: Store log file on seperate disks if you have more than 10 disks in your storage system.

Conclusion: Store the transaction log file on a seperate set of disks in case you have only 1 log file on these disks and you have more than 10 disks in your storage system.
: Tweet