Existem pontos de atenção e controle que são extremamente importantes para a boa administração de um ambiente de banco de dados, nesse caso especificamente em SQL Server.
Para uma boa introdução e até mesmo aprofundamento do tema relacionado a VLFs (Virtual Log Files), por favor veja abaixo alguns links interessantes:
- Controle Automático dos VLFs (Virtual Log Files)
- Fragmentação do Transaction Log – Parte I
- Fragmentação do Transaction Log – Parte II
- 8 Steps to better Transaction Log throughput
- Transaction Log VLFs – too many or too few?
Independente do tamanho do banco de dados, sendo ele um VLDB (Very Large Database) ou não, a fragmentação do log de transações pode causar impactos consideráveis à performance do banco de dados com relação a operações que envolvem escrita ou leitura no log de transações (TransactionLog.ldf), como por exemplo:
Por padrão, quando um novo banco de dados é criado no SQL Server, são criados dois arquivos físicos, o arquivo de Dados, que convencionou-se ser nomeado com a extensão “.mdf” e o arquivo do Log de Transações, que convencionalmente recebe a extensão “.ldf”. O arquivo do log de transações é internamente (e logicamente) dividido em VLFs, que são automaticamente criados quando há um crescimento (independente se manual ou automático) do arquivo físico.
Abaixo uma breve ilustração da divisão lógica de um Transaction Log file.
Fragmentação
Existem diversos tipos de fragmentação de um arquivo em disco do Windows, aqui tratar-se-á especificamente da fragmentação interna do arquivo de log.
Não existe um número mágico que possa ser usado como um threshold para dizer se o arquivo está ou não fragmentado, visto que depende muito das caracteristicas de carga e utilização do banco de dados nesse dado ambiente.
Em linhas gerais quantos menos VLFs, melhor para não se ter problemas relacionados a esse ponto.
Existem literaturas que citam números por volta de 250 a 500 VLFs como bons números para quantidades de VLFs em um banco de dados, particularmente, dependendo do cenário e de como o banco de dados é utilizado (alto ou baixo número de pequenas transações), acredito serem aceitáveis valores abaixo de 1000 VLFs por banco, para VLDBs.
VLDBs
Para bancos de dados muito grandes (acima de 1TB de dados), também cotidianamente citados como VLDBs (Very Large Database), consequentemente o arquivo de log de transações (.ldf) tende a ser igualmente grande, caso esse seja muito transacional, e também para suprir manutenções como por exemplo, rebuild de indices, que geram entradas no log de transações.
Nesses casos (na verdade, para bancos menores também) o ideal é que o arquivo de log seja pensado e planejado de modo que acomode todas as rotinas de manutenção, operação e backup de log sem que o mesmo fique sendo submetido a operações corriqueiras de crescimento (automático ou manual) e shrink, estando assim sempre em seu tamanho ideal e normal.
Exemplos de dimensionamento do Transaction Log File para VLDBs
Abaixo alguns exemplos de como podem ser dimensionados arquivos de log de transações com tamanhos pré-alocados de 1TB e também 2TB.
Legenda (Colunas)
- Growth(GB) – Tamanho do crescimento do arquivo de log (TamAtual).
- Qty_Per_Grow – Quantidade de VLFs criados a cada crescimento do arquivo.
- VLF Size (GB) – Tamanho de cada VLF criado com o crescimento anterior.
- Qty_total – Quantidade total de VLFs criados no final das operações descritas nessa linha para se alcançar o tamanho específico (nesse caso 1TB ou 2 TB).
- How_Many_Growths – Quantidade de operações de crescimento necessárias para se atingir os valores especificados.
- AVG_Time_To_Grow – Tempo médio de cada crescimento do arquivo de log, baseado no growth size.
Um possível grande problema quando da execução da manutenção acima, é que quaisquer operações de manipulação de arquivos do banco de dados, causam um lock que impede novas transações de serem executadas, um workaround bem simples para isso, é você criar um segundo arquivo de log de transações trabalhar seu arquivo conforme desejar, e em seguida excluir esse segundo arquivo (Créditos aqui para meu amigo Ariel Fernandez, que deu essa brilhante ideia), tudo com operações de ALTER DATABASE, que não necessariamente vão causar maiores impactos em seu ambiente.
Obviamente, na medida do possível é sempre recomendado que manutenções desse tipo sejam realizadas em janelas específicas fora dos horários de maior utilização do banco de dados.
Conclusão:
Eu me arriscaria a dizer que o assunto VLF além de ser negligenciado é também desconhecido da grande maioria dos DBAs. Com o potencial de problemas que pode causar é necessário que seja dada a devida atenção ao controle de quantidade de VLFs e fragmentação dos Virtual Log Files em seu banco de dados, evitando-se assim maiores problemas de performance decorrentes desse elemento.
Como está seu ambiente? Dever de casa cumprido? VLFs com quantidade baixa e tamanhos adequados ? Comenta ae…
Abraço,
Edvaldo Castro