O processo de CHECKPOINT

E ai pessoal, tudo bem com vocês?
Hoje quero falar sobre um dos muitos processos importantíssimos para o funcionamento do SQL Server, o CHECKPOINT.
Antes de entendermos o que é e quando acontece o processo de CHECKPOINT, precisamos ter em mente como o SQL Server trabalha internamente ao realizar comandos de DML (Data Manipulate Language).
Vamos imaginar que você queira fazer um UPDATE na sua tabela de Cliente, certo?
Observando a figura abaixo temos uma visão resumida do que ocorre internamente durante esse UPDATE:

1) O comando UPDATE é enviada para o SQL Server. Após a avaliação do comando UPDATE, o SQL Server busca no disco as páginas de dados que serão alteradas, coloca as mesmas em uma área de memória conhecida como BUFFER CACHE e  realiza o UPDATE;
2) Após o UPDATE ser realizado em memória o SQL Server começa um processo conhecido como WRITE-AHEAD, no qual as alterações realizadas nas páginas de dados localizadas em memória são armazenadas no arquivo de Transaction Log;
3) Para que as alterações realizadas em memória sejam definitivamente armazenadas no arquivo de dados é necessário um processo chamado CHECKPOINT.

Com certeza muitos de vocês estão pensando agora:

“Então o processo de CHECKPOINT verifica quais as transações comitadas já foram armazenadas com sucesso no arquivo de Transaction Log e pede para que o SQL Server armazene as páginas de dados destas transações, que estão em memória, no arquivo de dados”.

Bom, se você chegou nessa conclusão, está absolutamente certo, pois o processo de CHECKPOINT tem mesmo essa finalidade.
Mas será que eu estou certo em imaginar que alguns de vocês possam estar perguntando:

“Mas porque o SQL Server primeiro grava no arquivo de Transaction Log para depois esperar o CHECKPOINT levar as páginas alteradas para o arquivo de dados? Porque ele não grava direto da memória para os arquivos de dados? Não seria mais rápido?”

Uma excelente pergunta e que tem uma resposta ainda mais interessante. Imagine que você acabou de realizar com sucesso um UPDATE e após o término desse UPDATE o SQL Server já começa a escrever as páginas de dados que estão na memória diretamente no arquivo de dados. De repente……BUM…., lá se vai a força e seu servidor de banco de dados desliga.

O que você acha que vai acontecer quando a energia voltar e você ligar o servidor????
Sem querer ser o portador de más notícias, mas é bem provável que você terá um banco de dados corrompido, pois apenas uma parte do UPDATE foi feito no arquivo de dados.
Para garantir que esse cenário não aconteça, o SQL Server primeiro escreve as transações no arquivo de Transaction Log e para garantir que essas transações serão escritas no arquivos de dados o processo de CHECKPOINT acontece de tempos em tempos levando as páginas de dados alteradas para o arquivo de dados.

O SQL Server realiza o CHECKPOINT nos seguintes casos:

– Ao ser executado o comando de T-SQL CHECKPOINT;
– Quando o arquivo de Transaction Log estiver com mais de 70% de seu espaço preenchido e o banco de dados estiver com o Recovery Model Simple;
– De acordo com o valor (em minutos) definido na propriedade Recovery Interval da instância. O padrão dessa propriedade é que um CHECKPOINT ocorra a cada 1 minuto;
– Se o SQL Server for desligado através do comando SHUTDOWN, sem a clausúla NOWAIT.

Uma maneira interessante de se observar quando um CHECKPOINT acontece é iniciar o serviço do SQL Server com a trace flag 3502.
Com essa flag iniciada, podemos verificar nos logs da instância todo CHECKPOINT que acontece.

Bom galera, espero que tenha sido de alguma ajuda esse post.
Fiquem a vontade para comentar.

Grande abraço a todos.
Vitor Fava