Controlando seus checkpoints com a opção target_recovery_time_in_seconds no SQL Server 2012

No SQL Server temos o processo de checkpoint que “descarrega” as dirty pages da memória para o disco em certos intervalos.

Esse processo apesar de benéfico para o servidor em algumas situações pode ser problemático, pois pode causar um grande fluxo de IO em um momento inoportuno e causar lentidões.

Até o SQL Server 2008 R2 a frequência da ocorrência dos checkpoints só podia ser “controlada” através da opção “recovery interval” da instância, sendo esta base para o checkpoint em TODOS os bancos de dados.

No SQL Server 2012 foi adicionada uma nova forma de checkpoint chamada de “Indirect Checkpoint” que permite configurar o “recovery interval” para um determinado banco de dados de maneira diferente do padrão do servidor, dando assim maior controle ao DBA sobre esse processo. Vale lembrar que, para os bancos de dados onde essa opção não for configurada, ele continuará operando de acordo com a configuração em nível de servidor.

A sintaxe para alterar o recovery time para um banco de dados específico é bem simples:

ALTER DATABASE … SET TARGET_RECOVERY_TIME =target_recovery_time{ SECONDS | MINUTES }

você também pode alterar essa opção através da GUI do SSMS, nas propriedades do banco de dados em questão.

Mas então é só isso, tenho apenas uma opção para controlar melhor a frequência dos meus checkpoints? Na verdade, não!

O algoritmo atual dos checkpoints tem uma falha, pois considera o número de transações e não o número de dirty buffers para calcular a frequência dos checkpoints.

Sendo assim, caso você tenha poucas transações que alterem muitos dados, pode terminar com um tempo de recovery do banco de dados muito grande, mesmo com um recovery interval baixo. Então terminamos com um tempo de recovery não previsível, além de muitas vezes causar os temidos picos de IO no servidor, devido a essa falha.

Com os Indirect Checkpoints a quantidade de dirty pages é analisada por um novo processo em background, chamado de Recovery Writer, que verifica a dirty page list para decidir quando executar checkpoints, garantindo assim um processo muito mais preciso e evitando os indesejáveis picos de IO.

Vale a pena realizar alguns testes com essa nova opção, mesmo em cenários onde não hajam picos de IO, pois ela te permite um maior controle sobre o RTO de seus bancos de dados.

Obs (adicionado em 12/06/2012): antes do SQL Server 2012 você podia ativar o trace flag 3504 para que o SQL escreva no error log quando um checkpoint ocorrer, dando detalhes sobre o mesmo.

A partir do SQL Server 2012, além desse trace flag, o próprio SQL Server escreve no error log caso haja um checkpoint caso o “recovery interval” para o banco de dados em questão tenha sido excedido e serve como indicador de problemas em seu sistema de IO ou de uma má configuração no valor do “recovery interval”.

 

Para quem quiser mais detalhes técnicos recomendo os links abaixo:

Esse post foi publicado em Artigos, Virtual PASS BR. Bookmark o link permanente.

Uma resposta para Controlando seus checkpoints com a opção target_recovery_time_in_seconds no SQL Server 2012

  1. Pingback: Melhorias do T-SQL no SQL Server 2012 « Alex Souza

Deixe uma resposta

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