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.
Best Answer
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.