Páginas

segunda-feira, 3 de setembro de 2012

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!

2 comentários:

  1. Conseguiu mesclar a parte técnica com um texto bacana, muito fácil de entender.

    Tá de parabéns, Post bom e de grande valia.

    ResponderExcluir