Fragmentação do Transaction Log - Parte II

No post anterior, abordei sobre o Transaction Log, como ocorre sua fragmentação e como podemos identificá-la.

Como prometido, neste post vamos verificar como resolver a fragmentação interna do Log.

Mãos à massa!

Para exemplificar, vou criar os Databases LargeVLF_Full, LargeVLF_Simple e SmallVLF com as seguintes configurações para os arquivos de Log:

• LargeVLF_Full com Tamanho de 1MB e FileGrowth de 10% no recovery model FULL.
• LargeVLF_Simple com Tamanho de 1MB e FileGrowth de 10% no recovery model SIMPLE.
• SmallVLF com Tamanho de 8000MB e Filegrowth de 4000MB no recovery model FULL.

Após criar os Databases, vamos analisar como estão os VLFs dos arquivos de log através do comando DBCC LOGINFO.

• Database LargeVLF_Full com 4 VLFs de "256Kb".
• LargeVLF_Full com 4 VLFs de "256Kb".
• SmallVLF com 16 VLFs de 500MB.

Agora, vamos executar o script abaixo em todos os Databases.


Após a criação da tabela e execução dos inserts, vamos analisar como estão os arquivos de log de cada Database.

O Database LargeVLF_Full aumentou de 4 VLFs para 259 VLFs.
Observe que no modelo de recuperação Full, o status de todos os VLFs estão igual a 2 (Em uso).

Dica:
Você pode verificar o que está retendo o arquivo de log (status = 2) e impedindo a reutilização dos vlfs através das colunas log_reuse_wait e log_reuse_wait_desc na sys.databases.
Neste caso, é o Backup de Log!
As possíveis causas de retenção podem ser encontradas neste link.

O Database LargeVLF_Simple aumentou de 4 VLFs para 91 VLFs.
Pela característica do modelo de recuperação Simples, temos os status 0 (Livre) e 2 (Em uso).
No modelo Simple o log é automaticamente truncado, mas isso não o impede de ficar fragmentado. Cuidado!

Observe que o FileSize dos VLFs nos Databases LargeVLF_Full e LargeVLF_Simple são diferentes a cada crescimento. Isto é motivado pela configuração do FileGrowth em percentual.

O Database SmallVLF continua com os mesmos 16 VLFs.
Apenas o segundo e terceiro arquivo passaram a ser utilizados (Status = 2).

Podemos afirmar que os Databases LargeVLF_Full e LargeVLF_Simple estão fragmentados!
(Utilizo como "referência" o valor máximo de 50 para quantidade de VLFs. Fonte: Kimberly Tripp)

E em relação aos impactos das operações de Inserts e Updates mencionados no primeiro post por você Leandro?!
O arquivo de log não se beneficia do recurso Intant File Initialization. Quando é necessário o seu crescimento, seus Updates e Inserts ficarão bloqueados até a conclusão do crescimento do arquivo de log.

Vamos testar!
Para este teste, vamos realizar um update em todos os registros da Tabela nos Databases LargeVLF_Full e SmallVLF, medindo o tempo de execução.

A diferença entre os Updates é de 1896ms...

Para verificar o que motivou esta diferença, vamos ao Defult trace Log (Se não estiver desabilitado!).

Só para realizar o Auto Grow do arquivo de Log do Database LargeVLF_Full foram gastos 1486ms.
Dica: Evite crescimentos de arquivo do SQL Server durante o dia!

Bem, espero que tenha ficado claro!

Voltando para a resolução do problema de fragmentação...

Para "arrumar" nosso arquivo de log, devemos realizar os seguintes procedimentos:

1. Backup de Log.
2. Shirink do arquivo de Log.
3. Alteração do FileSize e AutoGrowth para um tamanho apropriado.

Para facilitar minha vida, criei o script "Analise de Fragmentação" que ao ser executado realiza as seguintes atividades:

1. Verifica e exibe os Databases que contenham mais de 50 VLFs.
2. Exibe o tamanho e o percentual de utilização dos arquivos de log através do comando DBCC SQLPERF(LOGSPACE).
3. Monta através de "Print", os comandos de ajustes para os Databases que apresentam mais de 50 VLFs.
• Checkpoint.
• Backup de Log se o modelo de recuperação não for Simple.
• Shirink (Recuperando o nome lógico do arquivo)
• Comando para alterar o FileSize e FileGrowth (Recuperando o nome lógico do arquivo)

Abaixo o retorno da query.

Após analisar o ambiente e copiar os comandos gerados para uma nova janela de query, informe:

1. FileSize e FileGrowth para os arquivos de Log identificados no script.
2. Execute o script de ajustes em um momento propício, ou seja, em uma janela de manutenção.
3. Execute novamente o script "Análise de fragmentação" para verificar se seu ambiente está normalizado.

Fazer o download do script aqui

Informação adicional
Ter poucos VLFs e de tamanho "considerável" também é prejudicial ao seu ambiente, pois irá impactar na liberação de espaço do arquivo de Log. VLFs de no máximo 400/500MB é o indicado.

Melhores práticas:

• Monitore seu ambiente.
• Escolha pelo modelo recuperação ideal para cada ambiente e necessidades de negócio.
• Ajuste o frequência de Backup de Log para controlar o tamanho do seu arquivo de Log.
• Não faça "BACKUP LOG ... WITH TRUNCATEONLY pois ele quebrará a sequência do Backup de Log.
• Utilize transações curtas para que não retenha os VLFs por muito tempo.
• Ajuste apropriadamente o FileSize e FileGrowth do seu arquivo de Log.
• Monitore seu ambiente.

E aí pessoal, como está o seu ambiente...

Espero que o post ajude.
Até o próximo!

Fragmentação do Transaction Log - Parte I

Constantemente, apesar de um vasto material sobre o assunto na net, me deparo com problemas de configuração do Transaction Log, tais como:

• Arquivos de Logs armazenados no mesmo disco que o arquivo de Dados.
• Uso de nível de RAID não recomendado.
• Falta de espaço em disco motivado pelo crescimento do arquivo de log.
• Mas principalmente, Arquivos de Log fragmentados.

A fragmentação do Transaction Log é um problema silencioso em seu ambiente e geralmente ocorre devido a configurações erradas para o arquivo do Transaction Log e em muitos casos, a utilização de configuração padrão (1MB para FileSize e AutoGrowth de 10%) para o arquivo de log, através de um CREATE DATABASE simples.

Este assunto motivou a última palestra que fiz com meu amigo Marcus Vinícius Bittencourt [Twitter | Blog] no SQL Saturday 147 em Recife e abordarei neste post.

Espero que ajude a alguém!

Introdução
Antes de entrarmos no universo do Transaction Log, preciso fazer uma pequena introdução de como os dados são trabalhados pelo SQL Server.
 
O Database Engine utiliza dois tipos de gravações: Lógica e Física.

• A gravação lógica ocorre quando os dados são alterados em uma página do Buffer Cache.
• A gravação física ocorrendo quando a página é armazenada no disco a partir do Buffer Cache.

A partir desta afirmação, temos a seguinte lógica de alteração de uma informação:

1. Se o dado a ser alterado não está em memória, ele é lido do disco e armazenado no Buffer Cache.
2. A alteração é efetuada no Buffer cache e a página é marcada como dirty pages (página suja).
3. O Database Engine utiliza o protocolo Write Ahead Log (WAL) para garantir que as dirtys pages sejam armazenadas primeiro no Transaction Log antes de serem persistidas em disco.
4. As dirtys pages são armazenadas em disco de modo assincrono através dos processos Eager Writing, Lazy Writing e Checkpoint.

A imagem abaixo, ilustra o que foi dito acima.

 







Transaction Log
De posse da informação de como os dados são persistidos, temos idéia da carga de trabalho do Transaction Log que o caracteriza como um componente crítico do SQL Server. 
Toda alteração realizada em um Database é registrada nele. 

Além de registrar as alterações e garantir o ACID, o Transaction Log é utilizado em:

• Database Mirroring
• Log Shipping
• Replicação Transacional
• ...

Não entrarei em detalhes do comportamento do Transaction Log em cada modelo recuperação, pois independente do modelo utilizado, o arquivo de log pode fragmentar.

Internamente, o log é dividido em pequenos blocos chamados de Virtual Log Files (VLF).
Além de ser a unidade de divisão do Transaction Log, o VLF permite o efeito circular do log através de sua reutilização.



 

O tamanho do VLF é definido automaticamente variando de acordo com o crescimento do Log.
A Tabela abaixo, informa como ocorre a divisão (Fonte: Kimberly Tripp)

• < 64MB = 4 VLFs
• > 64MB < 1GB = 8 VLFs
• > 1GB = 16 VLFs

Usando o exemplo de uma carga de dados que force um crescimento do arquivo de Log em 1000MB, podemos ter:

• Em um Growth de 10 MB => 4 VLFs de 2.5MB => Total de 400 VLFs.
• Em um Growth de 200MB => 8 vlfs de 25MB => Total de 40 VLFs
• Em um Growth de 1000MB => Total de 16 VLFs de 62.5MB

A partir do exemplo acima, fica claro que uma configuração ruim para o AutoGrowth do arquivo de Log gerará muitos VLFs, caracterizando o que chamamos de Fragmentação Interna do Transaction Log.

A fragmentação interna causa impactos em todos os processos que utilizam o Transaction Log e nas aplicações. (Sim... seus Updates e Inserts!!!)

Lembram que No início do post, informei a configuração default de criação do banco de dados. Lembra-se?

Tamanho Incial = 1MB com Growth = 10%.

Imagina um arquivo de log de alguns "Gigas" utilizando a configuração default...Ahhh!

Para verificar as informações de VLFs do arquivo de log, utilizamos o comando DBCC LOGINFO.
A quantidade de linhas retornadas é justamente a quantidade de VLFs existentes no Database..

Abaixo, informações das colunas retornardas pelo comando.



Destaque para as colunas:

• FileSize - Tamanho do VLF em Bytes.
• FSeqNo - Número que define a sequência de utilização dos VLFs.
• Status - Em uso = 2, Livre = 0
• CreateLSN - LSN no momento da criação dos VLFs. Quando igual a 0 (Zero) são VLFs criados no Create Database.

Ok. Já sabemos conceitualmente como o Transaction Log fica fragmentado e como analisar informações internas do arquivo de Log. Mas como resolver a fragmentação?

No próximo post, vamos colocar a mão na massa!

Até o próximo post!