Por que meu comando DELETE requer uma grande quantidade de armazenamento temporário de execução?

Há sete operadores no plano de consulta que podem se espalhar para tempdb. Eu os numerei abaixo:

A subconsulta select ArticleId from art.Article where PublishDate < @deleteDatefoi implementada como junção entre dois índices não clusterizados pelo otimizador de consulta. A junção é uma junção de hash que requer que uma tabela de hash seja construída no rótulo 1 . É possível que a tabela de hash seja derramada no tempdb. Para sua consulta, a tabela de hash tem apenas cerca de 100 mil linhas, portanto, não é provável que seja o problema.

A junção entre ArticleConcepte Articleé implementada como uma junção de mesclagem. Ambas as entradas de junção precisam ser classificadas para a junção que resulta na classificação vista no rótulo 2 . Essa classificação só precisa processar cerca de 100 mil linhas.

Uma classificação é feita no rótulo 3 para melhorar o desempenho da exclusão. Os dados serão classificados na ordem das chaves do índice clusterizado da tabela. Você está excluindo cerca de 12 milhões de linhas, então espero que classifique as chaves agrupadas de 12 milhões de linhas. Isso pode se espalhar para o tempdb.

A tabela de destino da exclusão possui índices não clusterizados. O otimizador de consulta tem alguns métodos diferentes para implementar as atualizações nos índices. Ele escolhe uma atualização ampla por índice . Isso é feito com base no custo e provavelmente ocorre porque você está excluindo uma grande porcentagem de linhas da tabela de destino. O spool de tabela no rótulo 4 contém todas as chaves de índice junto com as chaves de índice clusterizadas. Ele armazenará 12 milhões de linhas e gravará em tempdb.

As classificações nos rótulos 5 , 6 e 7 são para classificar os dados na ordem das chaves de índice e das chaves de índice clusterizado de cada índice não clusterizado. É provável que esses tipos estejam vazando para o tempdb.

Todos esses derramamentos se somam. Se você tiver um tipo de 1 GB de dados no disco e esse tipo de derramamento no disco, ele não consumirá necessariamente exatamente 1 GB de espaço tempdb. Na minha experiência, geralmente requer mais espaço no tempdb do que no disco.

Mesmo que a consulta não tenha falhado, ainda não é a abordagem mais ideal. A exclusão de 12 milhões de linhas de uma tabela de 14 milhões de linhas do índice clusterizado e três índices não clusterizados é muito trabalhoso. Seria mais eficiente inserir as linhas para manter em outra tabela, construir os índices não clusterizados nessa tabela e alternar as tabelas no lugar. Como você mesmo viu, descartar os índices não clusterizados antes da exclusão e recriá-los após a exclusão pode ser suficiente. As soluções alternativas descritas aqui só devem ser feitas durante uma janela de manutenção quando os usuários finais não estiverem acessando os dados.

Você precisa agrupar suas exclusões para não bloquear a tabela e preencher seu log de transações.

declare @deleteDate DATETIME2 = DATEADD(month, -3, GETDATE()) 
 DECLARE    
    @RC INT = 1;
 
WHILE (@RC > 0)
BEGIN
 
  delete top 1000 art.ArticleConcept 
   where ArticleId 
      IN (select ArticleId 
            from art.Article 
           where PublishDate < @deleteDate);
 
  SET @RC = @@ROWCOUNT
 
END

Você pode experimentar tamanhos de lote maiores (geralmente 5000-50000), mas este é um bom lugar para começar. Você precisa ter cuidado com as tentativas de escalonamento de bloqueio ao escolher tamanhos de lote.

Você provavelmente também verá uma melhoria enviando sua lista de ArticleIds para uma tabela temporária para que você só precise digitalizar art.Articleuma vez.

declare @deleteDate DATETIME2 = DATEADD(month, -3, GETDATE()) 

create table #ArticlesToDelete 
       (ArticleId int primary key not null);

insert into #ArticlesToDelete (ArticleId) 
select ArticleId 
  from art.Article 
 where PublishDate < @deleteDate;

DECLARE @RC INT = 1;
 
WHILE (@RC > 0)
BEGIN

  delete top 1000 A 
    from art.ArticleConcept A 
    join #ArticlesToDelete D 
      on A.ArticleId = D.ArticleId;

  SET @RC = @@ROWCOUNT

END

drop table #ArticlesToDelete

O principal problema é que não há coluna de data no art.ArticleConcept.

Além disso, você deve sempre compartilhar a estrutura da tabela de ambas as tabelas.

Excluir Bacthwise também é bom.

Acho que @Joe sugeriu o mesmo método abaixo

declare @deleteDate DATETIME2 = DATEADD(month, -3, GETDATE()) 

--Test this several time and mention column name instead of *
Select * into art.NewArticleConcept
from  art.ArticleConcept AC WITH (NOLOCK)
where exists 
(select ArticleId from art.Article A WITH (NOLOCK)
where a.ArticleId =ac.ArticleId and  PublishDate >= @deleteDate)

--Then
drop table art.ArticleConcept

--Then rename newly created table to old table name
sp_rename art.NewArticleConcept art.ArticleConcept

--Create index on art.ArticleConcept

    CREATE NONCLUSTERED INDEX [IDX_ArticleConcept_ArticleId_Incl_LexemId_Freq] 
 ON [art].[ArticleConcept]
    (
    [ArticleId] ASC
    )
    INCLUDE (   [LexemId],
    [Freq]) 
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, 
      SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, 
      ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)

A principal vantagem da abordagem acima é que você não precisa recuperar espaço ou reconstruir o índice ou reconstruir as estatísticas.

Se Select * intoa parte não for problemática, vá em frente.

Além disso, você tem que criar Covering indexna art.ArticleConcepttabela.