Início > SQL Server Internals > O processo de CHECKPOINT

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

Anúncios
  1. Fabrício
    16/03/2010 às 13:04

    Show de bola. Vitor, enquanto o checkpoint não acontece o SQL mantém essas páginas em memória. Em um ambiente 32 bits com muita alteração de dados e com pouca memória, fazer o checkpoint acontecer em um período menor que 1 minuto poderia ajudar na performance? Pois assim, essas páginas seriam liberadas mais rápidas.

  2. Fabiano Neves
    01/06/2010 às 20:25

    Show de explicação Vitor, parabéns.Abraços

  3. memset
    04/07/2012 às 10:02

    Ótimo post, me ajudou muito, dividindo idéias e multiplicando conhecimentos, abraço.

  4. Arthur Prado
    20/08/2015 às 15:32

    Boa tarde, tenho uma duvida, aqui na empresa ficam vários dias sem alterar a data de alteração nos arquivos do banco de dados, isso quer dizer que não ocorreu ainda o checkpoint correto? O que posso fazer para colocar o checkpoint para gravar a cada 3 minutos?

    • 20/08/2015 às 16:35

      Grande Arhur, tudo bem?

      Essa data de alteração que está visualizando é a do Windows?

      Se for esta data, não precisa se preocupar porque esta informação só será atualizada no caso de um reinicio do servidor ou então no redimensionamento dos arquivos.

      Grande abraço.

      • Arthur Prado
        21/08/2015 às 7:47

        Blz Vitor, mais enquanto não ocorre a alteração o processo do SQL server não continuaria aumentando uso de memória até ocupar toda a memória?

      • Marcelo
        02/09/2015 às 10:05

        Também estou com o mesmo problema. Já conseguiu alguma solução para este caso?

      • 09/09/2015 às 23:22

        Grande Marcelo, tudo bem?

        Ainda está com esse problema?

        Grande abraço.

      • Marcelo
        10/09/2015 às 9:52

        Bom dia Vitor, Obrigado pelo retorno.
        Quando realizei o comando Checkpoint, ele realizou a atualização do arquivo MDF. Mas não está fazendo automático depois disso. O arquivo de Log está quase o tamanho do banco. Estou utilizando Log Shipping.

      • 10/09/2015 às 11:35

        Marcelo,

        Parece que está com problemas de sincronismo com o LogShipping.

        O job de restore está funcionando?

        Grande abraço.

  5. Marcelo
    10/09/2015 às 16:04

    Vitor, agora está. Mas antes estava com o seguinte erro: Message
    The log shipping secondary database “NOME DO SERVIDOR” has restore threshold of 120 minutes and is out of sync. No restore was performed for 609 minutes. Restored latency is 0 minutes. Check agent log and logshipping monitor information.
    Para corrigir, refizemos novamente as configurações de Log Shipping e voltou a sincronizar com meu banco segundário.
    Será que ainda existe alguma coisa das configurações antigas? Existe alguma forma de limpar?

    • 10/09/2015 às 16:12

      Marcelo,

      Se você excluiu utilizando a própria configuração do LogShipping já está tudo limpo.

      Grande abraço meu amigo.

  6. Marcelo
    10/09/2015 às 16:15

    Vitor,
    Será porque ainda não atualiza o arquivo?

  7. Marcelo
    10/09/2015 às 18:04

    Desculpe Vitor, não ficou tão claro mesmo.
    Mesmo corrigindo, os parametros do Log Shipping, o arquivo de banco de dados não está atualizando o arquivo de dados MDF.
    Pelo verifiquei no Log de Erro, consta uma base de homologação que não existe:

    2015-09-10 16:40:00.31 spid203 The log shipping primary database (BASE DE DADOS.BASE_HOMOLOGACAO has backup threshold of 60 minutes and has not performed a backup log operation for 750580 minutes. Check agent log and logshipping monitor information.

  8. Luis Gustavo Uzai
    30/08/2016 às 20:16

    Boa noite Vitor, sei que o tópico é antigo mas estou com uma dúvida.
    Usando um banco de teste para fazer testes de integração.
    Então sobe um banco limpo, executa vários testes de integração persistindo dados e depois o banco é destruído.

    Gostaria de fazer algo diferente, manter o banco com os dados iniciais.
    Os testes fazem toda a persistência no banco e no final, em vez de destruir o banco simplesmente removo as alterações de Log sem executar o Checkpoint, isso é possível?

    Seria um método de manter um banco sempre com os dados iniciais sem ter que subir ele do zero. Da forma que os testes executam não funcionaria com transações.

  1. 14/01/2014 às 12:26

Deixe um comentário

Preencha os seus dados abaixo ou clique em um ícone para log in:

Logotipo do WordPress.com

Você está comentando utilizando sua conta WordPress.com. Sair / Alterar )

Imagem do Twitter

Você está comentando utilizando sua conta Twitter. Sair / Alterar )

Foto do Facebook

Você está comentando utilizando sua conta Facebook. Sair / Alterar )

Foto do Google+

Você está comentando utilizando sua conta Google+. Sair / Alterar )

Conectando a %s

%d blogueiros gostam disto: