Checkpoint SQL Server

Faaaaaala Galera,

Hoje vamos falar sobre um assunto altamente importante e que gera algumas duvidas conceituais…o famoso Checkpoint… bora lá…

images

Checkpoint é o processo interno da engine SQL Server responsável pela operação de gravação das transações/dados nos disco físico (arquivos .mdf e .ndf). É ele o mecanismo que cria o chamado ponto de verificação e grava as paginas com alterações de dados (Dirty Page) contidas no Transaction Log (.ldf) nos arquivos de dados (Data files .mdf e .ndf).

Como assim Luiz? Quando iniciamos uma transação, existe toda uma inteligência por trás que chamamos de  Query Life Cycle.

Para entendermos todo esse processo de transição entre arquivos de Log e Dados, vamos pensar a nível de vida de uma query.

Quando efetuamos uma operação DML (Insert, Update e Delete) essas ações são executadas primeiramente em memória (Buffer Cache), isso é feito por vários motivos, mas o principal é por performance, escrever em memoria é muito mais rápido do que escrever em disco.

Bom continuando o nosso raciocínio, assim que o DML é executado, a alteração está em memoria e em seguida a Engine SQL Server inicia um processo chamado Write-Ahead, que basicamente captura as paginas modificadas em memoria e grava no Transaction Log.

Neste momento, o INSERT que você executou está em memória e também no log. E a partir desse momento o Checkpoint começa a entrar em ação, passando pelo log e capturando todas as transações “comitadas” e gravando nos aquivos de dados (.mdf, .ndf).

O checkpoint pode ser executado de ou acionado de algumas formas:

  • AUTOMATIC: É sempre executado em segundo plano pelo SQL Server. O padrão é ser executado a cada 60 segundos. Mas isso pode ser alterado caso, 70% do espaço no ldf seja preenchido (em caso de Recorery Model Simple), ou seja, se o banco estiver recebendo muitas transações chegando ao ponto de preencher 70% do log em menos de 60 segundos, o checkpoint será executado.Para validar ou alterar o tempo default do checkpoint:

    EXEC sp_configurerecovery interval,seconds
    GO
    RECONFIGURE
    GO

  • MANUAL: Com o comando CHECKPOINT podemos ativar o processo manualmente. O checkpoint ocorrerá dentro do contexto de banco de banco de dados sob qual a conexão foi criada e não a nível de instancia. As vezes executamos o comando manualmente após algum script de carga que executamos ou para realização de algum teste onde queremos ter certeza que o dado estará em disco:

    USE seuDB
    GO

    CHECKPOINT
    GO

  • INTERNAL/INDIRECT: Executado por consequência de algum outro comando, como por exemplo: Backups, Create Snapshot, Shutdown (sem a clausula NOWAIT).

 

Resumindo…

  • (Automatic) Quando 70% do log(.ldf) for preenchido. (Em Recovery Model Simple). A cada 60 segundos. O que pode mudar de acordo com a carga de transações. (Os 70% são alcançados rapidamente).
  • (Manual) Manualmente com o comando Checkpoint.
  • (Internal) Com a execução de comandos como, Create Snapshot, Backup, entre outros. Quando ocorre Shutdown no SQL Server. Checkpoint ocorre em todos os databases.

 

Com o Trace Flag 3502é possível acompanhar a execuções do Checkpoint através do Errorlog.

Obs: Existem duas formas de ativar um Trace Flag:
A primeira e mais recomenda é incluindo uma linha com -T na configuração “Startup Paramaters”.  ex: -T3502
A Segunda é pelo comando DBCC TRACEON (n, -1). Ex: TRACEON (3502, -1).

Com o DBCC TRACESTATUS(-1); É possível visualizar os TFs ativos na instancia.
O DBCC TRACEOFF (n, -1) — desativa.
Exemplo: DBCC TRACEOFF (3502, -1);

 

Vamos fazer alguns testes para simular o comportamento do trace flag 3502. Segue Script.

obs: o Trace Flag 3605 é utilizado para output no SSMS. Mais sobre esse ele aqui.

Ativamos o TF, logo em seguida criamos um banco com uma tabela e executamos 100 Inserts.

DBCC TRACEON (3502, 3605, -1);
GO

CREATE DATABASE TesteCheckpoint
GO

USE TesteCheckpoint
GO

CREATE TABLE tb_teste
(
Id INT IDENTITY PRIMARY KEY,
[Text] VARCHAR(50) NOT NULL
)
GO

INSERT tb_teste
VALUES (‘Teste Checkpoint’)
GO 100

Após a execução, observe como ficou o errorlog.
Observe a ativação do TF e logo em seguida as entradas de registros do checkpoint.
o DBID 3 é o model, foi utilizado no momento do Create Database, e logo em seguida o DBID 7 que é o nosso banco “TesteCheckpoint”.

Com Trance Flag.JPG

Entre os motivos da existência do Checkpoint listados anteriormente, acredito que um dos principais também esteja ligado ao tempo de Recovery de um Banco de Dados após uma falha do servidor.

Quando iniciamos o serviço do SQL, todos os bancos entram em um processo chamado Recovery onde será realizado o Undo-Redo de cada banco.

Neste processo, o recovery ao iniciar um banco ele identifica o ultimo checkpoint e começar a partir dele a fazer um scan no Transaction log, com o objetivo de comitar ou não(rollback) as transações, para assim liberar o banco integro em status online.

Com isso, se o tempo entre o ultimo checkpoint até a falha for considerável, a possibilidade do Recovery demorar é grande e isso impacta no tempo de disponibilidade(SLA) do seu ambiente.

Por outro lado, podemos pensar assim, “Então vou alterar o checkpoint para a cada 1 seg”,  É bem provável que terá um problema de performance, pois seu Buffer Cache poderá ser prejudicado e isso impactará como um tiro no seu desempenho.

Então qual é a melhor configuração do Checkpoint? ahhh vou usar o Depende, isso varia muito de ambiente para ambiente, já vi casos em que mudaram a frequência devido a grande quantidade de transações  e também já vi caso onde foi preciso mudar devido a instabilidade de hardware do servidor, assim, perdendo performance mas mantendo um pouco mais de “segurança”.

Então por isso acredito que o depende é a melhor resposta. Cabe a nós analisar cada ambiente, cada banco e avaliar se é necessário alterar ou não. Por via de regra, deixe o padrão, o SQL Server vem sendo constantemente atualizado e a engine é bem inteligente nesse processo. Acredito que a alteração seria somente em casos de exceções.

 

Grande abraço …….[]s

 

Anúncios

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