Há muitas postagens de blog e artigos de práticas recomendadas exaltando as virtudes de colocar o arquivo de dados do SQL Server em um disco rígido e o log de transações em outro. A razão fornecida é que o arquivo de banco de dados terá leituras e gravações aleatórias, enquanto o log de transações terá apenas gravações sequenciais.
Mas e se você tiver centenas de bancos de dados? Existe um verdadeiro benefício de desempenho em colocar centenas de arquivos de log de transações em um disco separado? Se vários logs de transações estiverem sendo gravados, acho que as gravações do log de transações seriam tão aleatórias quanto as gravações do banco de dados.
Correto. Em teoria, se você tiver centenas de bancos de dados, precisará de centenas de unidades, uma para cada log. Na prática, embora ninguém se importe com esse caso, porque quando você tem centenas de bancos de dados, obviamente não espera um desempenho TPC de alto nível para cada banco de dados. Você provavelmente terá alguns bancos de dados com alta taxa de transferência e SLAs rigorosos e poderá tê-los em eixos separados, enquanto os muitos SLAs de nível inferior se acumulam em alguns discos compartilhados.
O log de transações é exatamente isso... um log em execução de todas as transações. Como tal, ele preenche continuamente em uma direção até ser marcado e substituído, ou truncado e substituído. A substituição é sequencial.
Considere seus arquivos de dados. Um registro de cliente pode conter um pedido de 5 anos atrás e um de 10 anos atrás. Se você excluir esses pedidos (digamos, você está arquivando dados em outro lugar), no tlog atual você exclui 1 e exclui 2, em ordem. No entanto, em seus arquivos de dados, você tocou em blocos que foram escritos há 5 e 10 anos.
Então com certeza importa :)
Colocar o log de transações em um disco separado por si só proporcionará melhor desempenho do que colocá-lo junto com outros arquivos de dados/log. Se você colocar todos os logs no mesmo disco, não estará fazendo IO sequencial, mas IO aleatório entre esses arquivos de log.