Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
add comment

3 Answers

up vote 7 down vote accepted

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.

share|improve this answer
add comment

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 :)

share|improve this answer
add comment

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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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