Partilhar via

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

  • Artigo

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 bom ponto conhecido a partir do 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 um desligamento ou uma falha inesperada.

Neste tópico:

  • Visão geral de pontos de verificação

  • Tarefas relacionadas

  • Conteúdo relacionado

Visão geral de pontos de verificação

Por razões de desempenho, o Mecanismo de Banco de Dados executa modificações nas páginas de banco de dados — no cache do buffer — e não grava essas páginas em 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 modificadas na memória atualmente (conhecidas como páginas sujas) e as informações do log de transações de memória em disco e, além disso, registra informações sobre o log de transações.

O Mecanismo de Banco de Dados oferece suporte para 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

Interface Transact-SQL

Descrição

Automático

EXEC sp_configure 'recovery interval', 'seconds'

Emitido automaticamente em segundo plano para seguir de tempo superior sugerido pela opção de configuração de servidor recovery interval. Pontos de verificação automáticos executados até a conclusão. Os pontos de verificação automáticos são acelerados 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 Configurar a opção recovery interval de configuração de servidor.

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ê usou ALTER DATABASE para definir TARGET_RECOVERY_TIME a >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 quando você executa um comando Transact-SQL CHECKPOINT. 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, 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 coincidam com o estado atual do log.
ObservaçãoObservação

A opção de configuração avançada -k SQL Server permite a um administrador de banco de dados acelerar o comportamento de E/S de ponto de verificação com base na taxa de transferência do subsistema de E/S de 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.

Observação importanteImportante

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

[Início]

Nesta seção:

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

  • Pontos de verificação automáticos

  • Pontos de verificação indiretos

  • Ponto de verificação interno

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

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

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 da 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 cada vez 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 servidor recovery interval. 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 de pontos de verificação automáticos depende da opção de configuração de servidor avançada recovery interval, 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 alcances de pontos de verificação automáticos atinge o 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, consulte 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 configuração de recovery interval 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.

[Início]

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

Para um sistema de processamento de transações online usando transações curtas (OLTP), o recovery interval é o fator primário que determina o tempo da recuperação. No entanto, a opção recovery interval não afeta o tempo necessário para desfazer uma transação de longa duração. A recuperação de um banco de dados com uma transação de longa duração pode levar mais tempo do que o especificado na opção recovery interval. Por exemplo, se uma transação demorada levou duas horas para executar atualizações antes que a instância do servidor fosse desabilitada, a recuperação real levará um tempo consideravelmente mais longo que o valor do recovery interval para recuperar a transação demorada. Para obter mais informações sobre o impacto de uma transação demorada em um tempo de recuperação, consulte 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. Esse método é importante porque, à medida que a configuração recovery interval aumenta, a recuperação de banco de dados demora mais tempo para ser concluída. Por exemplo, se você alterar recovery interval para 10, a recuperação levará aproximadamente 10 vezes mais tempo para conclusão que se recovery intervalfosse definido como zero.

[Início]

Pontos de verificação indiretos

Pontos de verificação indiretos, novos no SQL Server 2012, fornecem um nível de banco de dados configurável alternativo para 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:

  • Pontos de verificação indiretos podem reduzir o tempo de recuperação de banco de dados geral.

  • 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.

[Início]

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 com a interrupção do serviço SQL Server (MSSQLSERVER). Qualquer ação causa um ponto de verificação em cada banco de dados na instância de SQL Server.

  • Colocando uma FCI (instância de cluster de failover) SQL Server offline.

[Início]

Tarefas relacionadas

Para alterar o intervalo de recuperação em uma instância de servidor

Para configurar pontos de verificação indiretos em um banco de dados

Para emitir um ponto de verificação manual em um banco de dados

[Início]

Conteúdo relacionado

[Início]

Consulte também

Conceitos

O log de transações (SQL Server)