9

There are many blog posts and best practice articles extolling the virtues of placing the SQL Server data file on one hard drive and the transaction log on another. The reason given is that that the database file will be experiencing random reads and writes while the transaction log will only have sequential writes.

But what if you have hundreds of databases? Is there a true performance benefit to placing hundreds of transaction log files on a separate disk? If multiple transaction logs are being written to, then I would think the transaction log writes would be just as random as the database writes.

3 Answers 3

7

Correct. In theory, if you have 100s of DBs you need 100s of drives, one for each log. In practice though one does not care for such case, cause when you have 100s of DBs you obviously don't expect top-notch TPC performance for each DB. You will likely have some DBs with high throughput and stringent SLAs and you could have them each on separate spindles, while the many lower level SLAs cram on a few shared disks.

2

The transaction log is exactly that... a running log of all transactions. As such it fills continuously in one direction until checkpointed and overwritten, or truncated and overwritten. The overwriting is sequential.

Consider your data files. A customer record may contain an order from 5 years ago, and one from 10 years ago. If you delete those orders (say, you're archiving data elsewhere), in the current tlog you have delete 1, and delete 2, in order. However in your data files you have touched blocks that were written 5 & 10 years ago.

So it does for sure matter :)

2

Placing the transaction log on a separate disk by itself will give you better performance than placing it together with other data/log files. If you place all the logs on the same disk, you are not actually doing sequential IO, but random IO between these log files.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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