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

Artigo
10/13/2015

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

Visão geral de 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 um ponto de verificação periodicamente em cada banco de dados. Um ponto de verificação grava as páginas atuais modificadas na memória (conhecidas como páginas sujas) e as informações do log de transações de memória para disco e, além disso, 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áticos, indiretos, manuais e internos. A tabela a seguir resume os tipos de pontos de verificação.

Nome	Transact-SQL Interface	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. Pontos de verificação automáticos executados até a conclusão. Os pontos de verificação automáticos são limitados com base no número de gravações pendentes e se o Mecanismo de Banco de Dados detecta um aumento na latência de gravação acima de 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 cumprir um horário de recuperação de destino especificado pelo usuário para um determinado banco de dados. A hora de recuperação de destino padrão é 0, o que faz com que a heurística de ponto de verificação automático 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, veja Alterar o tempo de recuperação alvo de um banco de dados (SQL Server).
Manual	CHECKPOINT [ checkpoint_duration ]	Emitido quando um comando CHECKPOINT Transact-SQL é executado. O ponto de verificação manual ocorre no banco de dados atual para sua conexão. Por padrão, pontos de verificação manuais são executados até a conclusão. A aceleração funciona da mesma forma que para pontos de verificação automáticos. Opcionalmente, o parâmetro checkpoint_duration especifica a quantidade de tempo solicitada, em segundos, para a conclusão do ponto de verificação. Para obter mais informações, confira CHECKPOINT (Transact-SQL).
Interna	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 coincidam com o estado atual do log.

Observação

A -kopção de instalação avançada 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 configuração -k se aplica a pontos de verificação automáticos e a qualquer ponto de verificação manual e interno.

Para pontos de verificação automáticos, manuais e internos, somente as modificações feitas após o último ponto de verificação devem passar por roll forward durante a recuperação de banco de dados. Isso reduz o tempo necessário para recuperar um banco de dados.

Importante

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

Interação de TARGET_RECOVERY_TIME e opções 'recovery interval'

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

target_recovery_time	'recovery interval'	Tipo de ponto de verificação usado
0	0	pontos de verificação automáticos cujo intervalo de recuperação de destino é 1 minuto.
0	>0	Pontos de verificação automáticos, cujo intervalo de recuperação de destino é especificado pela configuração definida pelo usuário na opção sp_configurerecovery interval.
>0	Não aplicável.	Pontos de verificação indiretos cuja hora de recuperação de destino é determinada pela configuração de 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 ele pode processar durante o tempo especificado na opção de configuração do recovery interval servidor. Em todo banco de dados sem uma hora de recuperação de destino definida pelo usuário, o Mecanismo de Banco de Dados gera pontos de verificação automáticos. A frequência dos 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 pode processar no 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ções significativa terá mais pontos de verificação frequentes 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 será enfileirado se o log se tornar 70% cheio.

No modelo de recuperação simples, a menos que algum fator esteja atrasando o truncamento do log, um ponto de verificação automático trunca a seção não usada do log de transações. Em contrapartida, nos modelos de recuperação completo e bulk-logged, após o estabelecimento de uma cadeia de backup de log, os pontos de verificação automáticos não causam truncamento de log. Para obter mais informações, confira O log de transações (SQL Server).

Depois de uma falha do sistema, o tempo necessário para recuperar um determinado banco de dados depende muito 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. Não é possível determinar uma duração de recuperação precisa. Além disso, quando um ponto de verificação automático está em andamento, a atividade de E/S geral para os dados aumenta significativamente 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 prolongada 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 longa duração em um tempo de recuperação, confira O log de transações (SQL Server).

Normalmente, os valores padrão fornecem um ótimo desempenho de recuperação. No entanto, a alteração do intervalo de recuperação pode melhorar o desempenho nas circunstâncias seguintes:

Se, em geral, a recuperação demorar significativamente mais que 1 minuto quando transações demoradas não estão sendo revertidas.
Se você notar que os pontos de verificação frequentes estão prejudicando o desempenho em um banco de dados.

Se você decidir aumentar a configuração recovery interval, recomendamos fazer isso gradativamente em pequenos incrementos e avaliar o efeito de cada aumento incremental 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 aos pontos de verificação automáticos. No caso de uma falha de sistema, pontos de verificação indiretos fornecem um tempo de recuperação mais previsível potencialmente mais rápido do que os pontos de verificação automáticos. Os 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.
Pontos de verificação indiretos permitem a você controlar o tempo de recuperação de banco de dados de modo confiável, fatorando no custo de E/S aleatória durante REDO. Isso permite que uma instância de servidor permaneça em um limite superior nos tempos de recuperação para um determinado banco de dados (exceto quando uma transação demorada causa tempos UNDO excessivos).
Pontos de verificação indiretos reduzem os picos de E/S relacionados ao ponto de verificação gravando continuamente páginas sujas em disco em segundo plano.

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

Pontos de verificação internos

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

Os arquivos de banco de dados foram adicionados ou removidos usando ALTER DATABASE.
É realizado um backup de banco de dados.
Um instantâneo de banco de dados é criado, explicitamente ou internamente para DBCC CHECK.
É executada uma atividade que requer um desligamento de banco de dados. Por exemplo, AUTO_CLOSE está ON e a última conexão de usuário com o banco de dados está fechada ou é feita uma modificação de opção de banco de dados que requer o reinício do banco de dados.
Uma instância de SQL Server é interrompida interrompendo 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.
Colocação de uma FCI (instância de cluster de failover) do SQL Server offline.