O processo de CheckPoint - SQL Server (pt-BR) - TechNet Articles - United States (English)

Muitas pessoas pensam que ao realizar um commit suas alterações ja são gravadas em disco e não serão mais perdidas, bom, em relação a não serem mais perdidas, essas pessoas estão corretas, porem em relação a gravar em disco, não.

Table of Contents

Vamos pensar no fluxo de uma alteração:
As situações em que ocorre em CheckPoint (Forçado):
Mas, para que serve o arquivo de log?
Explicação:

Vamos pensar no fluxo de uma alteração:
- Usuario roda o select.
- DataBase Engine traz as paginas do disco para a memoria, uma vez que nenhuma alteração é feita em disco.
- Inicia a gravação no arquivo de log da base
- Altera os dados em memoria
- Se concluido com sucesso
Grava que foi com sucesso no log.
- Se concluida com erro
Grava que foi com erro no log.
- Se recovery model for simple, apaga o log dessa transação do arquivo de log, se não, não.

Ok, mas, aonde esta a gravação dos dados de memoria em disco? Bom!, agora que temos essas paginas na memoria alteradas, podemos chamar elas de Durty Buffer, ou seja, o conteudo de uma pagina que esta em memoria é diferente da mesma pagina em disco, existe um processo que roda em backupground, denominado CheckPoint, este processo é responsavel por fisicamente capturar os durty buffer da memoria e guardar em disco, para que os mesmos não sejam perdidos.

As situações em que ocorre em CheckPoint (Forçado):
- Execução explicita do CheckPoint
- Quando ha uma carga de dados em massa
- Adição ou remoção de arquivos de uma base pelo comando ALTER DATABASE
- Quando se para o serviço do SQL Server, todas as bases realizam um CheckPoint
- Quando é feito um backup da base de dados
- Desligamento de uma base

O SQL Server é configurado para automaticamente realizar as operações de CheckPoint, então ele realizara a mesma quando achar necessario, todo caso, é possivel fazer uma pequena intervenção, aumentando o numero maximo de tempo entre as operações de CheckPoint, lembrando que toda operação sera grava em log, e o processo de CheckPoint é responsavel por colocar os durty buffer em disco, portanto, é mais lento e necessita de I/O randomico, ja a escrita no log é I/O sequencial, que é muito mais rapido (Em meus testes, obtive um ganho de quase 200% de performance), portanto, aumentando este tempo, voce pode vir a ter um gargalo de memoria pois o SQL Server ira manter mais dados por mais tempo em memoria.

Mas, para que serve o arquivo de log?
O arquivo de log serve para, no caso de um desastre, os dados que estavam em memoria e não no disco, ficarem gravado em algum lugar, fazendo com que seja possivel a recuperação automatica do banco de dados no momento em que se subir o mesmo.

E como ele funciona? O SQL Server trabalha com um conceito de Fila de Logs ativos, abaixo tem uma foto, que esta no books online, que explica detalhadamente como funciona:

Explicação:
- Veja que temos 2 transações ativas: Tran1 e Tran2
- Dentro dessas alterações temos:
Um begin Tran
N comandos DML/DDL…..
Um commit/RollBack
- A ordem dos processos obedece uma sequencia logica:
Voce só pode realizar um rollbackup ou commit de uma transação ativa, portanto o begin tran deve vir antes do commit/rollback, as operações DML/DDL devem estar entre o begin tran e commit/rollback, se não, não ha o que comitar.
- Note tambem, que primeiro esta sendo efetuada a tran1, portanto, ela é o que chamados de MinLSN
(LSN = Logical Sequencial Number)
- Como o SQL Server apaga os logs sempre da transação mais antiga para a mais nova, o processo de limpeza de log ira sempre, buscar a transação MinLSN, porem, como as transações podem se intercalar (Como mostra a figura), caso a tran1 fique presa por falta de um commit ou rollback, a transação 2 não sera exclusa do log, mesmo que tenha feita um commit ou rollback, fazendo com que seu arquivo de log cresca sem diminuir, mesmo que em recovery model simple.
- Caso a tran1 seja liberada com commit ou rollback, a tran2 passara a ser a MinLSN, voltando todas as explicações anteriores, em relação as transações posteriores.

Originalmente escrito por:
Fabrizzio Antoniaci Caputo
MCITP SQL Server 2008 Administration
MCITP SQL Server 2008 Developer
Oracle OCA 11g
MCC, Moderador SQL Server
Twitter: @FabrizzioCaputo
Blog: http://fabrizziocaputo.wordpress.com