Apagar conjunto de linhas em tabelas enormes (delete)

p020_borrachaEu apago, tu apagas, ele apaga.
E tudo para!
Como apagar conjunto de linhas em tabelas enormes sem parar os outros processos que estão em execução simultânea e acessando a mesma tabela?

p020_sumario

1. O caso

Embora não muito frequente, nos fóruns de SQL Server às vezes aparecem tópicos citando que o processo de apagar linhas em tabelas VLT (very large table, tabela com grande quantidade de linhas) está travando os demais processos. Geralmente são tabelas que necessitam de limpeza periódica para apagar linhas anteriores a determinada data através da instrução DELETE. Entretanto, conforme consta na documentação da instrução DELETE, o comportamento padrão é o bloqueio exclusivo na tabela e com este bloqueio mantido até que a transação seja concluída. E é então que os demais processos começam a ficar lentos e até mesmo com a ocorrência de deadlock, sendo que nenhuma outra transação pode modificar os dados na tabela, embora operações de leitura possam ser realizadas desde que com o uso da dica de tabela NOLOCK ou nível de isolamento READ UNCOMMITTED.

Linhas e páginas. No SQL Server as linhas são armazenadas dentro de páginas e estas, as páginas, compõem a tabela. A menor unidade física que o SQL Server lê/grava na tabela é a página. Ou seja, nunca é lida ou gravada uma única linha mas sim uma página.

Sempre que algum comando solicita a leitura de uma linha, o mecanismo de armazenamento lê no disco a página em que está a linha e armazena essa página na memória do SQL Server (buffer cache) e a linha é então repassada para o processo que solicitou a leitura da linha. Esta é uma explicação simplificada do processo de leitura e quem tiver interesse em conhecer o mecanismo de leitura de páginas em detalhes pode consultar Reading Pages.

Apagamento de linhas. Uma operação de apagamento (DELETE) não remove fisicamente a linha da página mas sim a marca como “para apagar”. Assim que o COMMIT da transação é executado entra em ação o processo denominado ghost cleanup que realiza em background a ação de remoção da linha. Entretanto, o espaço físico ocupado anteriormente pelas linhas apagadas continua existindo nas páginas; a diferença é que ele foi alocado para “espaço disponível”.

Cenário deste artigo. De modo a simular essas condições, para desenvolvimento deste artigo foi considerada a existência da seguinte tabela, que armazena as batidas de ponto eletrônico dos funcionários de uma empresa:

-- código #1.1
-- Autor: José Diz 
-- Publicado em: Porto SQL - https://portosql.wordpress.com

CREATE TABLE tbPONTO (
     Id_Funcionario tinyint not null,
     Data_ref date not null,
     Turno tinyint not null,
     Entrada datetimeoffset(2) not null,
     Saida datetimeoffset(2) not null,
constraint PK_PONTO primary key (Id_Funcionario, Data_ref, Turno)
);

e que em determinados momentos é necessário apagar todas as linhas cuja data seja anterior a determinado valor.

Caso você queira acompanhar em seu computador os testes que serão apresentados ao longo do artigo, toda a coleção de códigos SQL deste artigo está reunida no arquivo “apagar linhas VLT.zip” (disponível para download), evitando assim a necessidade de ficar copiando e colando o texto dos códigos SQL.

Como informação adicional, o banco de dados utiliza o modelo de recuperação completo (full recovery model). Outro cuidado na realização dos testes foi o de garantir que os dados estejam no cache, de modo que características físicas de armazenamento não interfiram nos resultados. Além disso, estatísticas de execução foram coletadas, de modo a gerar quadros comparativos das abordagens apresentadas no artigo.

Antes de prosseguir, uma observação. Desenvolver códigos T-SQL exige tempo e dedicação para estudos e testes, afora conhecimento técnico. Sempre que utilizo código T-SQL de outra pessoa como base eu informo o nome do autor e/ou o endereço web da página onde foi publicado, de modo a respeitar o esforço de programação de quem o desenvolveu. Do contrário tem-se o plágio, que além de ser uma violação dos direitos autorais é também uma demonstração de falta de ética profissional. Nesse ponto faço minhas as palavras de Edvaldo Castro em seu artigo “PLÁGIO – Sério mesmo?”.

2. Abordagens

2.1. Apagamento único

A solução geralmente implementada é a seguinte:

-- código #2.1
-- Autor: José Diz 
-- Publicado em: Porto SQL - https://portosql.wordpress.com

declare @dataCorte date;
set @dataCorte= convert (date, '1/1/2011', 103);

DELETE tbPONTO
  where Data_ref < @dataCorte;

que é a solução mais simples para a necessidade.

Desta forma, quando o código #2.1 está em execução ocorre interrupção em outras transações que tentem atualizar a mesma tabela; inclusive o banco de dados podendo parar por falta de espaço na área alocada para o arquivo de log de transações, dependendo do espaço disponível para o arquivo de log de transações, do modelo de recuperação e do mecanismo de backup do arquivo de log de transações.

A execução do código #2.1 é bem rápida para tabelas com poucas linhas, mas para tabelas grandes a execução desse comando tem o efeito negativo relatado no caso: suspende os demais processos que necessitem de realizar transações de atualização na mesma tabela.

Este é o plano de execução do código #2.1:

p020_codigo #2.1

Temos então os seguintes valores:

Tabela ‘tbPONTO’. Número de verificações 1, leituras lógicas 12205, leituras físicas 0, leituras read-ahead 0.
Tempo de execução: 1,523 segundo

onde temos:

Número de verificações Quantidade de seeks ou scans após alcançar o nível folha em qualquer direção.
É 0 se o índice utilizado na pesquisa for um índice exclusivo (unique) ou índice clusterizado (primary key), a pesquisa envolve a chave primária e por somente um único valor.
É 1 se a pesquisa o índice utilizado for clusterizado mas não for pela chave primária.
É n, onde n é a quantidade de seeks ou scans ocorridos após a primeira pesquisa.
Leituras lógicas Quantidade de páginas lidas do cache.
Leituras físicas Quantidade de páginas lidas do disco para o cache.
Leituras read-ahead Quantidade de páginas lidas do disco e armazenadas no cache, mas sem terem sido solicitadas.

A explicação sobre o mecanismo de leitura de páginas está em Reading Pages.

Segundo informações do plano de execução, foram apagadas 219 mil linhas de um total de 1 milhão de linhas. O tempo gasto, 1,5 segundo, parece pouco mas é uma eternidade quando há outros processos parados, esperando a liberação da tabela tbPONTO.

2.2. Apagamento em blocos

Uma solução para mitigar os efeitos negativos citados anteriormente é apagar as linhas em blocos; por exemplo, a cada n linhas, onde n depende da quantidade de linhas da tabela. Para este artigo foram utilizadas 50.000 linhas para cada bloco, somente para reduzir o número de blocos e facilitar a demonstração dos resultados.

-- código #2.2
-- Autor: José Diz 
-- Publicado em: Porto SQL - https://portosql.wordpress.com

declare @QtdLinhas int, @dataCorte date;
set @QtdLinhas= 50000;
set @dataCorte= convert (date, '1/1/2011', 103);

declare @Apagados int;
set @Apagados= 1;
while @Apagados > 0
  begin

  BEGIN TRANSACTION;

  DELETE top (@QtdLinhas) tbPONTO
    where Data_ref < @dataCorte;

  IF @@rowcount < @QtdLinhas
    set @Apagados= 0;

  COMMIT;

  -- pausa entre apagamento de blocos
  waitfor delay '00:00:05'; -- 5 segundos

  end;

@@ROWCOUNT retorna quantas linhas foram apagadas na execução da instrução DELETE. A cada execução do bloco é solicitado que 50.000 linhas sejam apagadas; se o valor retornado por @@ROWCOUNT for 50.000, significa que há mais linhas a apagar mas se o valor for inferior ao limite então temos que foi o último bloco de linhas apagadas e deve-se interromper a execução do laço.

Pode-se dar uma pausa entre o apagamento de cada bloco, de modo a mitigar ainda mais o efeito negativo em outros processos; no código SQL isso foi implementado com o comando WAITFOR.

Este é o plano de execução do código #2.2:

p020_codigo #2.2

Para o código #2.2 foi transcrito neste artigo somente uma das execuções, pois todas têm o mesmo esquema de operadores. Não se percebe diferença entre a sequência de operadores dos planos de execução dos códigos #2.1 e #2.2, embora teoricamente o código #2.2 reduza o tempo de bloqueio dos demais processos em execução em paralelo, pois o tempo de cada execução é menor.

Entretanto, como o predicado na cláusula WHERE utiliza coluna que não possui índice, é certo que às custas de maior número de leituras na tabela tbPONTO para encontrar as linhas a apagar; isto em cada execução do laço (WHILE). Obteve-se então os seguintes valores de execução:

Bloco 1
Tabela ‘tbPONTO’. Número de verificações 1, leituras lógicas 2519, leituras físicas 0, leituras read-ahead 0.
Tempo de execução: 0,520 segundo
Bloco 2
Tabela ‘tbPONTO’. Número de verificações 1, leituras lógicas 3503, leituras físicas 0, leituras read-ahead 0.
Tempo de execução: 0,613 segundo
Bloco 3
Tabela ‘tbPONTO’. Número de verificações 1, leituras lógicas 4312, leituras físicas 0, leituras read-ahead 0.
Tempo de execução: 0,643 segundo
Bloco 4
Tabela ‘tbPONTO’. Número de verificações 1, leituras lógicas 5289, leituras físicas 0, leituras read-ahead 0.
Tempo de execução: 0,757 segundo
Bloco 5
Tabela ‘tbPONTO’. Número de verificações 1, leituras lógicas 4365, leituras físicas 0, leituras read-ahead 0.
Tempo de execução: 0,640 segundo

Do relatório acima o que se observa de imediato é que a cada bloco o número de leituras lógicas é maior. Outra observação é que o tempo de execução aumenta, à medida que mais blocos são apagados. Como são lidas/apagadas o mesmo número de linhas linhas a cada execução, poderia-se esperar que os números de leituras a cada execução fossem próximos entre si mas tal fato não ocorreu; qual então a causa?

No primeiro bloco, para apagar 50 mil linhas tiveram que ser lidas 225.725 linhas, conforme informação coletada nas propriedades do operador Clustered Index Scan. Como se sabe, a tabela tbPONTO possui chave primária composta pelas colunas { Id_Funcionario, Data_ref, Turno }. Desta forma, em cada página as linhas estão ordenadas fisicamente por estas colunas, o que significa que todas as linhas de cada página são lidas para validar o predicado, até atingir o limite de linhas a apagar.

Supondo existência da tabela DATAS com os valores a seguir e que a chave primária seja composta pelas colunas { Id_Funcionario, Data_ref }, ao comandar o apagamento das 3 primeiras linhas cujo valor de Data_ref seja do ano 2000 (ou anterior) e utilizando o seguinte comando:

DELETE top (3) DATAS
  where Data_ref < '20010101'

p020_quadro 6

a terceira linha a apagar somente é encontrada após a leitura de nona linha.

Retornando ao código #2.2, o seguinte quadro foi montado coletando informações da execução de cada bloco:

p020_quadro 1

Nele se observa a quantidade de linhas que tiveram que ser lidas em cada bloco para encontrar as linhas a apagar.

Até o momento o único ganho foi que o tempo em que o código SQL está em transação é menor (comparando-se o 1,5 segundo obtido na execução do código #2.1 com os valores de cada etapa de execução do código #2.2), reduzindo assim o impacto negativo em outros processos que estejam executando em paralelo.

Como o índice existente da tabela tbPONTO é pelas colunas { IdFuncionario, Data_ref, Turno } e o apagamento é pela coluna Data_ref, isto explica porque até o momento não se encontrou uma solução eficiente que não implique em leitura sequencial na tabela tbPONTO.

2.3. Lista de chaves a apagar, em blocos

Como o critério de corte não utiliza coluna da chave primária, talvez seja o caso de obter previamente os valores de chave primária das linhas que devem ser apagadas, armazená-las em uma tabela temporária e a seguir utilizar o conteúdo desta tabela temporária para filtrar as linhas que serão apagadas.

Exemplificando com a tabela DATAS do item anterior, seria algo como criar tabela temporária contendo as seguintes linhas:

p020_quadro 7

e a seguir executar o comando:

DELETE D
  from DATAS as D
       inner join #APAGAR as A 
          on A.Id_Funcionario=D.Id_Funcionario
             and A.Data_ref = D.Data_ref;

Ou seja, a tabela temporária #APAGAR contém somente as linhas a apagar, evitando – teoricamente – a necessidade de ler sequencialmente a tabela DATAS para encontrar as linhas a apagar, a cada bloco de execução.

A implementação de tal abordagem na tabela tbPONTO é a seguinte:

-- código #2.3
-- Autor: José Diz 
-- Publicado em: Porto SQL - https://portosql.wordpress.com

-- apaga tabela temporária, se houver
IF Object_id ('tempDB..#APAGAR') is not null
  DROP TABLE #APAGAR;
go

-- parâmetros de execução
declare @QtdLinhas int, @dataCorte date;
set @QtdLinhas= 50000;
set @dataCorte= convert (date, '1/1/2011', 103);

-- cria tabela temporária com identificação das chaves
CREATE TABLE #APAGAR (
     seq int identity,
     Id_Funcionario tinyint,
     Data_ref date, 
     Turno tinyint
constraint I1_APAGAR primary key (Id_Funcionario, Data_ref, Turno)
);

INSERT into #APAGAR (Id_Funcionario, Data_ref, Turno)
  SELECT Id_Funcionario, Data_ref, Turno
    from tbPONTO
    where Data_ref < @dataCorte          
    order by Id_Funcionario, Data_ref, Turno;     

UPDATE STATISTICS #APAGAR;     

--   
declare @Apagados int, @I int; 
set @I= 1; 

set @Apagados= 1; 
while @Apagados > 0
  begin

  BEGIN TRANSACTION;

  DELETE P
    from tbPONTO as P
         inner join #APAGAR as A on A.Id_Funcionario = P.Id_Funcionario
                                    and A.Data_ref = P.Data_ref
                                    and A.Turno = P.Turno
    where A.seq between @I and (@I + @QtdLinhas -1)
          and P.Data_ref < @dataCorte; -- !!

  IF @@rowcount < @QtdLinhas 
    set @Apagados= 0; 

  COMMIT; 

  -- pausa entre apagamento de blocos 
  waitfor delay '00:00:05'; -- 5 segundos 

  set @I+= @QtdLinhas; 
  end; 

-- apaga tabela temporária 
IF Object_id ('tempDB..#APAGAR') is not null 
  DROP TABLE #APAGAR;

Este é o plano de execução de cada bloco no código #2.3:

p020_codigo #2.3

O que se observa é a presença do operador MERGE JOIN, que efetua a leitura sequencial nas duas tabelas. De acordo com a documentação, “If the two join inputs are not small but are sorted on their join column (for example, if they were obtained by scanning sorted indexes), a merge join is the fastest join operation” e “… if the data volume is large and the desired data can be obtained presorted from existing B-tree indexes, merge join is often the fastest available join algorithm”.

Para obter as estatísticas de execução foram acrescentadas instruções específicas no código #2.3 (vide anexo), obtendo-se então os seguintes valores:

Carga da tabela #APAGAR
Tabela ‘tbPONTO’. Número de verificações 1, leituras lógicas 4678, leituras físicas 0, leituras read-ahead 0.
Bloco 1
Tabela ‘tbPONTO’. Número de verificações 1, leituras lógicas 2714, leituras físicas 0, leituras read-ahead 0.
Tabela ‘#APAGAR’. Número de verificações 1, leituras lógicas 490, leituras físicas 0, leituras read-ahead 0.
Tempo de execução: 5,526 segundos
Bloco 2
Tabela ‘tbPONTO’. Número de verificações 1, leituras lógicas 3687, leituras físicas 0, leituras read-ahead 0.
Tabela ‘#APAGAR’. Número de verificações 1, leituras lógicas 490, leituras físicas 0, leituras read-ahead 0.
Tempo de execução: 3,207 segundos
Bloco 3
Tabela ‘tbPONTO’. Número de verificações 1, leituras lógicas 4492, leituras físicas 0, leituras read-ahead 0.
Tabela ‘#APAGAR’. Número de verificações 1, leituras lógicas 490, leituras físicas 0, leituras read-ahead 0.
Tempo de execução: 2,870 segundos
Bloco 4
Tabela ‘tbPONTO’. Número de verificações 1, leituras lógicas 5423, leituras físicas 0, leituras read-ahead 0.
Tabela ‘#APAGAR’. Número de verificações 1, leituras lógicas 490, leituras físicas 0, leituras read-ahead 0.
Tempo de execução: 3,097 segundos
Bloco 5
Tabela ‘tbPONTO’. Número de verificações 1, leituras lógicas 4459, leituras físicas 0, leituras read-ahead 0.
Tabela ‘#APAGAR’. Número de verificações 1, leituras lógicas 490, leituras físicas 0, leituras read-ahead 0.
Tempo de execução: 2,626 segundos

Montando o quadro de comparação entre as duas abordagens (apagamento simples em blocos e lista de chaves a apagar), temos o seguinte:

p020_quadro 2

Ou seja, com relação a leituras lógicas de páginas e linhas lidas praticamente não há diferenças entre as duas abordagens (códigos #2.2 e #2.3), mas com relação aos tempos decorridos em cada bloco temos tempos absurdamente mais elevados do que os coletados na execução do código #2.2.

Neste artigo a proporção de linhas a apagar é de 2:10 (2 linhas a apagar para cada 10 linhas da tabela); isto talvez explique a escolha do operador MERGE JOIN pelo otimizador de consultas (query optimizer). Talvez se a proporção entre linhas a apagar e tamanho da tabela fosse menor, outro operador fosse escolhido e esta abordagem se mostrasse eficiente.

Aquela expectativa de que utilizando uma lista de chaves a apagar poderia tornar o processo mais rápido foi por água abaixo… Pelo menos no cenário deste artigo.

2.4. Último apagado, em blocos

Pesquisei então na web sobre artigos relacionados a apagar grande quantidade de linhas. Achei vários, mas boa parte utilizando técnicas que dependem da existência de índice pela coluna que será utilizada como delimitador do que será apagado (que no caso deste artigo é a coluna Data_ref). Entretanto, em um dos artigos encontrei solução que se aplica ao caso deste artigo. O título do artigo éA more efficient DELETE using TOP, CTEs, OUTPUT, and somebody else’s clever idea”. A técnica é algo semelhante à apresentada no item anterior, com relação a utilizar uma tabela auxiliar. Enquanto que na técnica “Lista de chaves a apagar” a tabela é preenchida anteriormente ao apagamento, neste caso a tabela passa a registrar o que foi apagado e com a vantagem de ser menor, pois a cada bloco apagado a tabela auxiliar tem seu conteúdo também apagado. Para registrar na tabela auxiliar as linhas apagadas utiliza-se a cláusula OUTPUT.

-- código #2.4
-- Autor: José Diz 
-- Publicado em: Porto SQL - https://portosql.wordpress.com

-- apaga tabela temporária, se houver
IF Object_id ('tempDB..#APAGADO') is not null
  DROP TABLE #APAGADO;
go

-- parâmetros de execução
declare @QtdLinhas int, @dataCorte date;
set @QtdLinhas= 50000;
set @dataCorte= convert (date, '1/1/2011', 103);

-- cria tabela temporária com identificação das chaves apagadas
CREATE TABLE #APAGADO (
     Id_Funcionario tinyint, seq int identity
);

-- insere pseudo apagado
INSERT into #APAGADO (Id_Funcionario) values (0);

declare @Ult_Id_Funcionario tinyint;

--
declare @Apagados int;

set @Apagados= 1;
while @Apagados > 0
  begin

  -- obtém Id_Funcionario da última linha apagada
  SELECT top (1) @Ult_Id_Funcionario= Id_Funcionario
    from #APAGADO
    order by seq desc;
  TRUNCATE TABLE #APAGADO;

  --
  BEGIN TRANSACTION;

  with Le_Linhas as (
  SELECT top (@QtdLinhas) Id_Funcionario, Data_ref, Turno
    from tbPONTO
    where Data_ref < @dataCorte                                            and Id_Funcionario >= @Ult_Id_Funcionario
    order by Id_Funcionario, Data_ref, Turno
  )
  DELETE
    from Le_linhas 
    output deleted.Id_Funcionario into #APAGADO;

  IF @@rowcount < @QtdLinhas
    set @Apagados= 0;

  COMMIT;

  -- pausa entre apagamento de blocos
  waitfor delay '00:00:05'; -- 5 segundos

  end;

-- apaga tabela temporária
IF Object_id ('tempDB..#APAGADO') is not null
  DROP TABLE #APAGADO;

Este é o plano de execução do código #2.4:

p020_codigo #2.4

e as estatísticas de acesso às tabelas

Bloco 1
Tabela ‘#APAGADO’. Número de verificações 0, leituras lógicas 50086, leituras físicas 0, leituras read-ahead 0.
Tabela ‘tbPONTO’. Número de verificações 1, leituras lógicas 2519, leituras físicas 0, leituras read-ahead 0.
Tempo de execução: 1,203 segundo
Bloco 2
Tabela ‘#APAGADO’. Número de verificações 0, leituras lógicas 50086, leituras físicas 0, leituras read-ahead 0.
Tabela ‘tbPONTO’. Número de verificações 1, leituras lógicas 2674, leituras físicas 0, leituras read-ahead 0.
Tempo de execução: 1,057 segundo
Bloco 3
Tabela ‘#APAGADO’. Número de verificações 0, leituras lógicas 50086, leituras físicas 0, leituras read-ahead 0.
Tabela ‘tbPONTO’. Número de verificações 1, leituras lógicas 2602, leituras físicas 0, leituras read-ahead 0.
Tempo de execução: 1,093 segundo
Bloco 4
Tabela ‘#APAGADO’. Número de verificações 0, leituras lógicas 50086, leituras físicas 0, leituras read-ahead 0.
Tabela ‘tbPONTO’. Número de verificações 1, leituras lógicas 2709, leituras físicas 0, leituras read-ahead 0.
Tempo de execução: 1,097 segundo
Bloco 5
Tabela ‘#APAGADO’. Número de verificações 0, leituras lógicas 19032, leituras físicas 0, leituras read-ahead 0.
Tabela ‘tbPONTO’. Número de verificações 1, leituras lógicas 875, leituras físicas 0, leituras read-ahead 0.
Tempo de execução: 0,483 segundo

Comparando o plano de execução do código #2.4 com o do código #2.2, o que se observa é que a leitura na tabela tbPONTO passou a iniciar logo após a última linha apagada no bloco anterior, com o custo estimado reduzido de 32% para 17%. Inclusive o número de leituras lógicas na tabela em cada bloco foi reduzido para o mínimo necessário; este foi um ótimo ganho.

Por outro lado agora há a presença do operador Table Insert para inserir na tabela #APAGADO a identificação das linhas que foram apagadas na execução.

Além disso, o número de leituras lógicas na tabela #APAGADO está elevado; qual o motivo? O quadro de comparação entre os códigos #2.2 e #2.4 ficou então assim:

p020_quadro 3

Até o momento parece que a melhor abordagem é o de apagamento em blocos sem qualquer tentativa de otimização (código #2.2), pois é o que fica menos tempo bloqueando a tabela tbPONTO. Entretanto, a técnica descrita neste item se mostrou interessante e que pode ser útil em determinados cenários.

2.5. Com índice pela coluna filtro, em blocos

Depois de tentativas diversas, como seria a solução se houvesse índice pela coluna de corte, isto é, pela coluna Data_ref? Para saber, só criando o índice e reexecutando as abordagens anteriores. Mas antes disso algumas considerações sobre a criação do índice pela coluna de corte:

  • aumento na ocupação de espaço físico em disco, pois será índice do tipo nonclustered;
  • carga adicional para o database engine pois terá que manter esse índice atualizado.

O código SQL de criação do índice é bem simples:

-- código #2.5 
CREATE nonclustered INDEX I2_tbPONTO on tbPONTO (Data_ref);

Não é necessário incluir as colunas Id_Funcionario e Turno, pois as colunas da chave primária são automaticamente incluídas para tabelas indexadas.

No artigo “DELETE TOP x rows avoiding a table scan” há uma dica interessante, que é o de utilizar uma tabela derivada para efetuar o apagamento das linhas. Isto é, a instrução DELETE apaga o resultado da tabela derivada e não a tabela original; essa mesma abordagem consta em “Using the TOP option with modifications”.

-- código #2.6
-- Autor: José Diz 
-- Publicado em: Porto SQL - https://portosql.wordpress.com

declare @QtdLinhas int, @dataCorte date;
set @QtdLinhas= 50000;
set @dataCorte= convert (date, '1/1/2011', 103);

declare @Apagados int;
set @Apagados= 1;
while @Apagados > 0
  begin

  BEGIN TRANSACTION;

  with Bloco as (
  SELECT top (@QtdLinhas) Id_Funcionario, Data_ref, Turno
    from tbPONTO
    where Data_ref < @dataCorte
    order by Data_ref
  )
  DELETE from Bloco;

  IF @@rowcount < @QtdLinhas
    set @Apagados= 0;

  COMMIT;

  -- pausa entre apagamento de blocos
  waitfor delay '00:00:05'; -- 5 segundos

  end;

O plano de execução é o seguinte:

p020_codigo #2.6

Após execução do código #2.6, obteve-se o seguinte quadro comparativo:

p020_quadro 4

3. Observações

3.1. Observações iniciais

O artigo teve como objetivo encontrar a melhor forma de apagar conjunto de linhas em tabelas enormes, impactando o menos possível nos demais processos em execução simultânea. À medida que os testes se desenvolviam foi possível perceber que não há “a melhor” mas sim um elenco de técnicas dentre as quais deve-se escolher aquela que retorne melhores resultados para cada caso.

O quadro comparativo de tempo decorrido em segundos para cada técnica, por bloco, é o seguinte:

p020_quadro 5

É curioso observar que as estratégias utilizadas nos itens 2.3 a 2.5 não representaram ganho, se considerarmos o cenário e os resultados dos testes deste artigo. Aliás, todos representaram piora, principalmente o código #2.5, com valores elevadíssimos.

É necessário considerar que os testes foram realizados com os dados na memória, para que não houvesse interferência do fator armazenamento nos resultados. Para quem se interessar, uma sugestão é limpar o cache de páginas antes da execução de cada código e refazer os testes, medindo assim o impacto da leitura física. De qualquer forma, deve-se ter em mente que leituras sequenciais ativam o mecanismo de read ahead, o que agiliza o processo de carregamento do cache de páginas e reduzem a influência da necessidade de ler a tabela no disco.

Apagamento em blocos. Considerando-se o cenário de testes deste artigo, a solução que mantém por menos tempo a tabela bloqueada é o apagamento em blocos, sem utilização de qualquer técnica adicional. Fato: a cada execução mais linhas têm que ser lidas para encontrar o próximo bloco a apagar.

Lista de chaves a apagar, em blocos. A tabela #APAGAR foi criada com índice pelas colunas { Id_Funcionario, Data_ref, Turno }. Para o cenário deste artigo esta opção se mostrou péssima, com elevados tempos de bloqueio.

Último apagado, em blocos. Embora no cenário deste artigo os resultados desta técnica tenham se mostrado pouco piores do que o do apagamento em blocos, observou-se que as linhas que já foram lidas anteriormente não são lidas novamente, para encontrar o próximo bloco a apagar; isto me pareceu uma vantagem. Outro ponto positivo observado é que o tempo de apagamento de cada bloco é constante, o que pode ser vantajoso para tabelas enormes (very large tables, VLT), principalmente quando o percentual de apagamento for reduzido ou para apagar pequenos blocos.

Com índice pela coluna filtro, em blocos. Algo curioso ocorreu nos testes deste item, que utiliza índice pela coluna Data_ref. Ao acrescentar a opção de recompilação logo após a instrução DELETE,

DELETE from Bloco
  option (RECOMPILE);

o plano de execução gerado me pareceu mais coerente com a programação do código #2.6. Esta abordagem teve como resultado de que para cada bloco somente são lidas as linhas que serão apagadas devido ao acesso através do operador Index Seek, pelo índice nonclustered, embora para isso tenha ocorrido elevadíssimo número de leituras lógicas.

3.2. Quando o Lock escalation entra em cena

3.2.1. Blocos pequenos

Na documentação da instrução DELETE consta que a tabela fica bloqueada em modo exclusivo enquanto em execução: “By default, a DELETE statement always acquires an exclusive (X) lock on the table it modifies, and holds that lock until the transaction completes”. Entretanto, ao ler o item “Modifying in chunks”, do livro T-SQL Querying, deparei com trecho em que o autor cita o mecanismo de ocorrência de lock escalation no processamento da instrução DELETE. Ora, se ocorre lock escalation é porque a tabela não é bloqueada de imediato pela instrução DELETE, conforme consta na documentação. Segundo Itzik Ben-Gan, “SQL Server will attempt to escalate from the initial granularity of locks (row or page) to a table lock (or partition, if configured)”. E agora vem o principal: “The first trigger for SQL Server to attempt escalation is when the same transaction reaches 5,000 locks against the same object. (…) When escalation succeeds, the transaction locks the entire table (or partition) until it finishes”.

Consultando o livro Microsoft SQL Server 2012 Internals encontrei a mesma informação:

Lock escalation occurs in the following situations:

  • The number of locks held by a single statement on one object, or on one partition of one object, exceeds a threshold. That threshold is currently 5,000 locks (…);
  • Memory taken by lock resources exceeds 24 percent of the memory used by the database engine”.

Então, para evitar o bloqueio exclusivo de toda a tabela basta apagar n linhas em cada execução da instrução DELETE, onde n deve ser menor do que 5.000. Ou seja, a combinação de

  1. apagamento em blocos; junto com a
  2. definição de tamanho de bloco em tal valor de linhas de modo que o lock escalation não ative o bloqueio da tabela.

3.2.2. set LOCK_ESCALATION

Mas se o objetivo é evitar o bloqueio da tabela tbPONTO por causa do lock escalation, outra opção é desativar o mecanismo através da propriedade LOCK_ESCALATION da tabela:

   ALTER TABLE tbPONTO set (LOCK_ESCALATION = disable);

e em cada execução da instrução DELETE configurar a opção de bloqueio por linha. Por exemplo:

   DELETE top (@QtdLinhas) tbPONTO
     with (ROWLOCK, XLOCK, HOLDLOCK);

Ou seja, teríamos bloqueio por linha e não mais a possibilidade do bloqueio de toda a tabela. Considerando-se que é a instrução DELETE, talvez a opção XLOCK seja redundante; na dúvida, ela consta no comando.

Mas é necessário ter em mente que ao desativar o lock escalation para a tabela os demais programas que não tenham o cuidado de apagar linhas em pequenos blocos podem gerar uma quantidade tal de bloqueios individuais por linha que o SQL Server fique sobrecarregado ao manipular a lista de bloqueios de objetos, pois em cada bloqueio de objeto são gastos 128 bytes. Para o cenário deste artigo, em que são apagadas 219 mil linhas, teremos então alocação de 26,7 MB da memória para registrar os bloqueios ativos. Conforme consta no livro Microsoft SQL Server 2012 Internals, “Because a finite amount of memory is available for the lock structures, escalation is sometimes necessary to ensure that the memory for locks stays within reasonable limits”.

Deve-se avaliar com atenção a possibilidade de substituir o bloqueio por linha (ROWLOCK) pelo bloqueio por página (PAGLOCK). A princípio pode parecer interessante realizar o bloqueio por página (PAGLOCK) no lugar de linha (ROWLOCK), mas é necessário saber quantas linhas serão apagadas em cada página; se a proporção for reduzida, pode-se ter um elevado número de páginas bloqueadas para poucas linhas a apagar, aproximando-se então da situação que ocorre quando do bloqueio de tabela.

3.3. Observações finais

Como mencionado no início do artigo, a coluna utilizada para decidir quais linhas a apagar não faz parte da chave primária e nem mesmo de outro índice; isto implica na ocorrência de leitura sequencial para encontrar as linhas a apagar. Então, os testes foram direcionados para solucionar essa situação.

O contexto deste artigo foi o de descrever abordagens de apagamento de conjunto de linhas tendo como objetivo reduzir o impacto em outros processos em execução no momento. Das informações obtidas, a forma mais simples seria desativar o lock escalation, utilizar o bloqueio por linha e apagar as linhas em uma única execução DELETE (abordagem “Apagamento único”). Desta forma somente as linhas a apagar seriam bloqueadas e pode-se dizer que não haveria nenhum impacto aos demais processos. Entretanto, há o risco de colapso na lista de bloqueios de objetos.

O que posso deduzir ao final deste artigo é que não há definição de qual abordagem é a melhor, pois depende do contexto. Ao analisar as opções, o que se deve ter como objetivo é reduzir ao máximo o impacto nos demais processos concorrentes, escolhendo: uma das abordagens apresentadas; o tamanho de cada bloco; e a desativação ou não do lock escalation, em conjunto com o bloqueio adequado (linha ou página). E muitos testes até encontrar a abordagem específica para o caso.

Porcentagem de linhas a apagar. Outro ponto que se deve analisar antes de qualquer decisão é conhecer qual é a porcentagem de linhas a apagar dentro do total de linhas da tabela; dependendo desse percentual a solução mais simples pode ser (ou não) a criação de uma nova tabela somente com as linhas remanescentes, apagar a tabela atual, refazer as ligações da nova tabela com as demais tabelas. É claro que isso somente é possível se for possível parar o banco de dados para efetuar o swap das tabelas.

Ainda com relação ao percentual de linhas a apagar, deve-se analisar a existência de índices nonclustered e avaliar se é mais rápido apagar estes índices, apagar as linhas da tabela e recriar os índices anteriormente apagados; ou não.

Compactação das páginas. Uma operação de apagamento (DELETE) não remove fisicamente a linha da página mas sim a marca como “para apagar” e o processo denominado ghost cleanup realiza o processo de remoção, após o COMMIT. Entretanto, o espaço físico ocupado anteriormente pelas linhas apagadas continua existindo nas páginas; a diferença é que ele foi alocado para “espaço disponível”. Dependendo da quantidade de linhas apagadas por página pode ser recomendado realizar a tarefa de compactar a tabela, evitando assim que as páginas lidas ocupem espaço no buffer mas que tenham poucas linhas.

Linha bloqueada. Pode ocorrer que o processo de apagamento seja suspenso caso alguma linha (ou página que contém a linha) a ser apagada esteja bloqueada por outro processo. Para contornar o primeiro caso, linha a apagar bloqueada por outro processo, uma opção é ignorá-la, deixando-a para ser apagada na próxima execução de bloco, no loop WHILE; isto é possível com a dica de tabela READPAST. Ao utilizá-la, o código SQL deve ser alterado para tratar os efeitos. Em tempo, esta opção não pode ser empregada nas técnicas “Lista de chaves a apagar” e “Último apagado”.

Log de transações. O artigo não tratou do impacto no log de transações, embora sobre esse assunto tenha sido publicado o artigo Break large delete operations into chunks, de Aaron Bertrand.

Tratamento de erro. Para simplificação do artigo, nos códigos SQL não foram implementados tratamento de erro mas no código em produção deve-se ter este cuidado.

4. Fontes de consulta

4.1. Artigos

4.2. Documentação SQL Server

4.3. Livros

PDF
Este artigo também está disponível em arquivo no formato PDF, o que possibilita tanto visualizar online quanto obter cópia (download) para leitura offline e até mesmo impressão. Clique no texto abaixo para obter o artigo completo.

Apagar linhas em tabelas enormes

Deixe um comentário

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 )

Foto do Google

Você está comentando utilizando sua conta Google. 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 )

Conectando a %s