Início / dba / Perguntas / 166112
Accepted
Alf47
Asked: 2017-03-04 08:47:36 +0800 CST

Usando CHECKPOINT vs GO

  • 772

Normalmente, quando um dos desenvolvedores ou analistas de dados precisa realizar uma atualização ou exclusão muito grande de dados (onde um truncar ou truncar/inserir não faria sentido porque o conjunto de dados a ser mantido é muito grande) recomendo que eles façam algo como o seguinte:

-- Delete 1 million rows 1 thousand at a time
DELETE TOP (1000) FROM TableA WHERE <condition>
WAITFOR DELAY '00:00:01'
GO 1000

O resultado disso em bancos de dados que estão em modo de recuperação total é que 1) a espera permite que outras transações sejam processadas se necessário e 2) quando o backup de log é executado é capaz de marcar as operações já concluídas como sujas no arquivo de log para que pode reutilizar o espaço e evitar que o tronco cresça muito rapidamente.

Em vez de fazer isso, gostaria de saber se é possível realizar a mesma coisa usando pontos de verificação. Essa declaração resultaria efetivamente na mesma situação?

-- Delete 1 million rows 1 thousand at a time
WHILE EXISTS ( SELECT 1 FROM TableA WHERE <condition> )
BEGIN
DELETE TOP (1000) FROM TableA WHERE <condition>
WAITFOR DELAY '00:00:01'
CHECKPOINT
END

Novamente, esses são bancos de dados em modo de recuperação total.

sql-server delete

2 respostas

  1. Best Answer

    A coisa mais importante ao fazer uma grande atualização ou exclusão é evitar que o log de transações fique fora de controle. Para permitir a reutilização do log e evitar o inchaço no Tlog ( truncamento do log de transações )

    • No modo de recuperação total - faça backups de log
    • No modo de recuperação simples - faça pontos de verificação.

    Como você está usando a recuperação completa, sua lógica de exclusão deve ser

    begin
 begin tran
   -- delete using batches 
   -- carefully choose batch size. SQL Server will do a lock escalation 
   -- for batch greater than 5000. (https://technet.microsoft.com/en-us/library/ms184286(v=sql.105).aspx)
 commit tran / rollback tran 
  -- since you are using Full recovery, take log backups
  -- with CHECKPOINT SQL Server still cannot reuse those portions of the log until you perform a log backup.
end

    Eu uso waitfor delaypara esp. evitar bloqueios prolongados.

    Consulte esta postagem no blog da mina de ouro - Divida grandes operações de exclusão em pedaços por Aaron Bertrand. Além disso, Tome cuidado ao criar scripts em lotes é uma postagem de blog muito útil.

    • 2

  2. Os dois são equivalentes e você está confundindo lote com truncamento de log.

    O importante é que o código seja executado em lotes para que os bloqueios sejam liberados e outras sessões possam entrar e ler a tabela (e também para que o arquivo de log possa limpar quando o banco de dados estiver no modelo de recuperação simples).

    No entanto - em seu primeiro exemplo, WAITFOR provavelmente não é necessário - todas as sessões estão enfileiradas esperando e provavelmente entrarão entre cada execução.

    O segundo é equivalente, exceto que informa aos bancos de dados do modo SIMPLE que o log pode ser limpo, no entanto, é provável que isso aconteça a cada poucos segundos de qualquer maneira! No modelo de recuperação FULL, nenhum deles está agregando muito valor.

    • 1

relate perguntas