Pontos de verificação de banco de dados (SQL Server)

Este tópico fornece uma visão geral dos pontos de verificação do banco de dados do SQL Server. Um ponto de verificação cria um ponto bom conhecido do qual o Mecanismo de Banco de Dados do SQL Server pode começar a aplicar alterações contidas no log durante a recuperação após um desligamento inesperado ou uma falha.

Visão geral dos pontos de verificação

Por motivos de desempenho, o Mecanismo de Banco de Dados executa modificações em páginas de banco de dados na memória no cache do buffer e não grava essas páginas no disco após cada alteração. Em vez disso, o Mecanismo de Banco de Dados emite periodicamente um ponto de verificação em cada banco de dados. Um ponto de verificação grava as páginas modificadas na memória atuais ( conhecidas como páginas sujas) e as informações de log de transações da memória para o disco e, também, registra informações sobre o log de transações.

O Mecanismo de Banco de Dados dá suporte a vários tipos de pontos de verificação: automático, indireto, manual e interno. A tabela a seguir resume os tipos de pontos de verificação.

Nome	Interface Transact-SQL	Descrição
Automático	EXEC sp_configure *'`recovery interval`','`seconds`'*	Emitido automaticamente em segundo plano para atender ao limite de tempo superior sugerido pela opção de configuração do `recovery interval` servidor. Os pontos de verificação automáticos são executados até a conclusão. Os pontos de verificação automáticos são controlados com base no número de gravações pendentes e na detecção, pelo Mecanismo de Banco de Dados, de um aumento na latência de gravação superior a 20 milissegundos. Para obter mais informações, consulte Configure the recovery interval Server Configuration Option.
Indireto	ALTER DATABASE... SET TARGET_RECOVERY_TIME =target_recovery_time { SECONDS \| MINUTES }	Emitido em segundo plano para atender a um tempo de recuperação alvo especificado pelo usuário para um determinado banco de dados. O tempo de recuperação do alvo padrão é 0, o que faz com que a heurística automática de ponto de verificação seja usada no banco de dados. Se você tiver usado ALTER DATABASE para definir TARGET_RECOVERY_TIME como >0, esse valor será usado, em vez do intervalo de recuperação especificado para a instância do servidor. Para obter mais informações, consulte Alterar o tempo de recuperação de destino de um banco de dados (SQL Server).
Manual	CHECKPOINT [ checkpoint_duration ]	Emitido ao executar um comando Transact-SQL CHECKPOINT. O ponto de verificação manual ocorre no banco de dados atual referente à sua conexão. Por padrão, os pontos de verificação manuais são executados até a conclusão. A limitação funciona da mesma maneira que para pontos de verificação automáticos. Opcionalmente, o parâmetro checkpoint_duration especifica um tempo solicitado, em segundos, para que o ponto de verificação seja concluído. Para obter mais informações, consulte CHECKPOINT (Transact-SQL).
Interno	Nenhum.	Emitido por várias operações de servidor, como backup e criação de instantâneo de banco de dados, para garantir que as imagens de disco reflitam o estado atual do log.

Observação

A -kopção de instalação avançada do SQL Server permite que um administrador de banco de dados limite o comportamento de E/S do ponto de verificação com base na taxa de transferência do subsistema de E/S para alguns tipos de pontos de verificação. A opção de instalação -k se aplica a pontos de verificação automáticos e a todos os pontos de verificação manuais e internos sem restrições.

Para pontos de verificação automáticos, manuais e internos, somente as modificações feitas após o ponto de verificação mais recente precisam ser aplicadas durante a recuperação do banco de dados. Isso reduz o tempo necessário para recuperar um banco de dados.

Importante

Transações não confirmadas de execução prolongada aumentam o tempo de recuperação para todos os tipos de pontos de verificação.

Interação das opções TARGET_RECOVERY_TIME e 'intervalo de recuperação'

A tabela a seguir resume a interação entre a configuração global do servidor sp_configure'recovery interval' e a configuração específica do banco de dados ALTER DATABASE ... TARGET_RECOVERY_TIME.

TEMPO_ALVO_DE_RECUPERAÇÃO	'intervalo de recuperação'	Tipo de ponto de verificação usado
0	0	pontos de verificação automáticos cujo intervalo de recuperação alvo é de 1 minuto.
0	>0	Pontos de verificação automáticos cujo intervalo de recuperação alvo é especificado pela configuração definida pelo usuário da opção intervalo do sp_configurerecovery.
>0	Não aplicável.	Pontos de verificação indiretos cujo tempo de recuperação alvo é determinado pela configuração específica TARGET_RECOVERY_TIME, expressa em segundos.

Pontos de verificação automáticos

Um ponto de verificação automático ocorre sempre que o número de registros de log atinge o número que o Mecanismo de Banco de Dados estima que pode processar durante o tempo especificado na opção de configuração do recovery interval servidor. Em cada banco de dados sem um tempo de recuperação de destino definido pelo usuário, o Mecanismo de Banco de Dados gera pontos de verificação automáticos. A frequência de pontos de verificação automáticos depende da opção de configuração avançada do recovery interval servidor, que especifica o tempo máximo que uma determinada instância de servidor deve usar para recuperar um banco de dados durante uma reinicialização do sistema. O Mecanismo de Banco de Dados estima o número máximo de registros de log que ele pode processar dentro do intervalo de recuperação. Quando um banco de dados que está usando pontos de verificação automáticos atinge esse número máximo de registros de log, o Mecanismo de Banco de Dados emite um ponto de verificação no banco de dados. O intervalo de tempo entre pontos de verificação automáticos pode ser altamente variável. Um banco de dados com uma carga de trabalho de transação substancial terá pontos de verificação mais frequentes do que um banco de dados usado principalmente para operações somente leitura.

Além disso, no modelo de recuperação simples, um ponto de verificação automático também é enfileirado se o log ficar 70% cheio.

No modelo de recuperação simples, a menos que algum fator esteja atrasando o truncamento de log, um ponto de verificação automático trunca a seção não utilizada do log de transações. Por outro lado, nos modelos de recuperação completos e bulk-logged, uma vez que uma cadeia de backup de log tenha sido estabelecida, os pontos de verificação automáticos não causam truncamento do log. Para obter mais informações, confira O log de transações (SQL Server).

Após uma falha no sistema, o tempo necessário para recuperar um determinado banco de dados depende em grande parte da quantidade de E/S aleatória necessária para refazer páginas que estavam sujas no momento da falha. Isso significa que a recovery interval configuração não é confiável. Ele não pode determinar uma duração de recuperação precisa. Além disso, quando um ponto de verificação automático está em andamento, a atividade geral de E/S para dados aumenta de forma significativa e de forma bastante imprevisível.

Impacto do intervalo de recuperação no desempenho de recuperação

Para um sistema OLTP (processamento de transações online) usando transações curtas, recovery interval é o fator principal que determina o tempo de recuperação. No entanto, a opção recovery interval não afeta o tempo necessário para desfazer uma transação de execução prolongada. A recuperação de um banco de dados com uma transação de execução prolongada pode levar muito mais tempo do que a especificada na opção recovery interval . Por exemplo, se uma transação de execução longa levou duas horas para executar atualizações antes que a instância do servidor fosse desabilitada, a recuperação real levará consideravelmente mais tempo do que o recovery interval valor para recuperar a transação longa. Para obter mais informações sobre o impacto de uma transação de execução prolongada no tempo de recuperação, consulte O Log de Transações (SQL Server).

Normalmente, os valores padrão fornecem um desempenho de recuperação ideal. No entanto, alterar o intervalo de recuperação pode melhorar o desempenho nas seguintes circunstâncias:

Se a recuperação rotineiramente demorar significativamente mais de 1 minuto quando transações de longa duração não estiverem sendo revertidas.
Se você observar que pontos de verificação frequentes estão comprometendo o desempenho em um banco de dados.

Se você decidir aumentar a configuração recovery interval, recomendamos que faça isso gradualmente em pequenos incrementos e avalie o efeito de cada um no desempenho de recuperação. Essa abordagem é importante porque, à medida que a configuração aumenta, a recovery interval recuperação do banco de dados demora muitas vezes mais para ser concluída. Por exemplo, se você alterar recovery interval 10, a recuperação levará aproximadamente 10 vezes mais tempo para ser concluída do que quando recovery interval está definida como zero.

Pontos de verificação indiretos

Os pontos de verificação indiretos, novos no SQL Server 2012, fornecem uma alternativa configurável no nível do banco de dados para pontos de verificação automáticos. No caso de uma falha no sistema, os pontos de verificação indiretos fornecem um tempo de recuperação potencialmente mais rápido e previsível do que os pontos de verificação automáticos. Pontos de verificação indiretos oferecem as seguintes vantagens:

Uma carga de trabalho transacional online em um banco de dados configurado para pontos de verificação indiretos pode apresentar degradação no desempenho. Pontos de verificação indiretos garantem que o número de páginas sujas esteja abaixo de certo limite, para que a recuperação do banco de dados seja concluída dentro da meta do tempo de recuperação. A opção de configuração do intervalo de recuperação usa o número de transações para determinar o tempo de recuperação em relação aos pontos de verificação indiretos, que usam o número de páginas sujas. Quando pontos de verificação indiretos são habilitados em um banco de dados que recebe um grande número de operações DML, o gravador em segundo plano pode iniciar eliminação agressiva de buffers sujos no disco para garantir que o tempo necessário para executar a recuperação esteja dentro da meta do tempo de recuperação definido para o banco de dados. Isso pode causar atividade adicional de E/S em determinados sistemas, o que pode contribuir para um gargalo de desempenho, se o subsistema do disco estiver operando acima ou próximo do limite de E/S.
Os pontos de verificação indiretos permitem controlar de forma confiável o tempo de recuperação do banco de dados, considerando o custo de E/S aleatória durante o REDO. Isso permite que uma instância de servidor permaneça dentro de um limite máximo de tempos de recuperação para um determinado banco de dados (exceto quando uma transação de longa duração causa tempos excessivos de desfazer).
Os pontos de verificação indiretos reduzem a E/S relacionada ao ponto de verificação, gravando continuamente páginas sujas no disco em segundo plano.

No entanto, uma carga de trabalho transacional online em um banco de dados configurado para pontos de verificação indiretos pode enfrentar degradação de desempenho. Isso ocorre porque o gravador de plano de fundo utilizado pelo ponto de verificação indireto às vezes aumenta a carga de gravação total em uma instância de servidor.

Pontos de verificação internos

Pontos de verificação internos são gerados por vários componentes do servidor para garantir que as imagens de disco correspondam ao estado atual do log. O ponto de verificação interno é gerado em resposta aos seguintes eventos:

Os arquivos de banco de dados foram adicionados ou removidos usando ALTER DATABASE.
Um backup de banco de dados é feito.
Um instantâneo de banco de dados é criado, seja explicitamente ou internamente para DBCC CHECK.
Uma atividade que exige um desligamento de banco de dados é executada. Por exemplo, AUTO_CLOSE é ON e a última conexão de usuário com o banco de dados é fechada ou é feita uma alteração de opção de banco de dados que requer uma reinicialização do banco de dados.
Uma instância do SQL Server é parada ao parar o serviço SQL Server (MSSQLSERVER). Qualquer ação causa um ponto de verificação em cada banco de dados na instância do SQL Server.
Colocar uma instância de cluster de failover (FCI) do SQL Server offline.