Why does my DELETE command require huge amount of temporary run storage?

Question

I am trying to perform a cleanup operation on a table using DELETE and I receive the following error:

Could not allocate space for object 'dbo.SORT temporary run storage: 140767697436672' in database 'tempdb' because the 'PRIMARY' filegroup is full. Create disk space by deleting unneeded files, dropping objects in the filegroup, adding additional files to the filegroup, or setting autogrowth on for existing files in the filegroup.

Before running the DELETE I have more than 11G of free disk space. When the error is issued, I have next to nothing on that partition. Context information comes below:

1) Problematic query:

declare @deleteDate DATETIME2 = DATEADD(month, -3, GETDATE()) 
delete from art.ArticleConcept where ArticleId IN (select ArticleId from art.Article where PublishDate < @deleteDate)

2) Cardinality for involved tables

declare @deleteDate DATETIME2 = DATEADD(month, -3, GETDATE())
select count(1) from art.Article    -- 137181
select count(1) from art.Article where PublishDate < @deleteDate    -- 111450
select count(1) from art.ArticleConcept where ArticleId IN (select ArticleId from art.Article where PublishDate < @deleteDate)      -- 12153045
exec sp_spaceused 'art.ArticleConcept'
-- name             rows       reserved     data        index_size   unused
-- ArticleConcept   14624589   1702000 KB   616488 KB   1084272 KB   1240 KB

3) Indexes

-- index_name   index_description   index_keys
-- IDX_ArticleConcept_ArticleId_Incl_LexemId_Freq   nonclustered located on PRIMARY ArticleId

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)

4) Server

Select @@version
-- Microsoft SQL Server 2014 - 12.0.2000.8 (X64) 
-- Feb 20 2014 20:04:26 
-- Copyright (c) Microsoft Corporation
-- Express Edition (64-bit) on Windows NT 6.3 <X64> (Build 9600: ) (Hypervisor)

5) Execution plan (estimated)

I understand that I am performing a large DELETE, but I cannot understand why it requires so much space to do it: the whole ArticleConcept table has less than 2GB (reserved space), yet to remove records from it requires more than 11GB.

Question: Why does my DELETE command require huge amount of temporary run storage?

I have removed all secondary indexes and I could perform the DELETE. However, why so much more space is needed to perform the DELETE when having them, looks strange to me.

I am trying to delete 12,153,045 out of 14,624,589 records (quite many). I have not monitored the transaction log, but once I received an error related to it:

The transaction log for database ... is full due to 'ACTIVE_TRANSACTION'

Answer 1

There are seven operators in the query plan that could spill to tempdb. I numbered them below:

The subquery select ArticleId from art.Article where PublishDate < @deleteDate was implemented as join between two nonclustered indexes by the query optimizer. The join is a hash join which requires a hash table to be built at label 1. It's possible for the hash table to spill to tempdb. For your query the hash table only has around 100k rows so it's not likely to be the issue.

The join between ArticleConcept and Article is implemented as a merge join. Both join inputs need to be sorted for the join which results in the sort seen at label 2. This sort only has to process around 100k rows.

A sort is done at label 3 in order to improve performance of the delete. The data will be sorted in order of the keys of the clustered index of the table. You're deleting around 12 million rows, so I expect that sort the clustered keys of 12 million rows. This can spill to tempdb.

The target table of the delete has nonclustered indexes. The query optimizer has a few different methods for implementing the updates to the indexes. It chooses a wide, per-index update. This is done on a cost basis and is likely occurring because you're deleting a large percentage of rows from the target table. The table spool at label 4 contains all of the index keys along with the clustered index keys. It will store 12 million rows and it will write to tempdb.

The sorts at labels 5, 6, and 7 are to sort the data in order of the index keys and the clustered index keys of each nonclustered index. It's likely that these sorts are spilling to tempdb.

All of those spills add up. If you have a sort of 1 GB of data on disk and that sort spills to disk it does not necessarily consume exactly 1 GB of tempdb space. In my experience it often requires more space in tempdb than it does on disk.

Even if the query didn't fail it's still not the most optimal approach. Deleting 12 million rows from a 14 million row table from the clustered index and three nonclustered indexes is a lot of work. It would be more efficient to insert the rows to keep into another table, build the nonclustered indexes on that table, and to switch the tables in place. As you have seen yourself, dropping the nonclustered indexes before the delete and recreating them after the delete may be good enough. The workarounds described here should only be done during a maintenance window when end users aren't accessing the data.

Answer 2

You need to batch your deletes so that you don't lock the table and fill your transaction log.

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

You can experiment with larger batch sizes (I usually go 5000-50000) but this is a good place to start. You do have to be careful about lock escalation attempts when choosing batch sizes.

You'll probably also see an improvement by sending your list of ArticleIds to a temp table so that you only have to scan art.Article one time.

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

Answer 3

Main problem is there is no date column in art.ArticleConcept.

Also you should always share table structure of both table.

Bacthwise delete is also good.

I think @Joe has suggested same method like below

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)

Main advantage of above approach is that ,you do not have reclaim space or rebuild index or rebuild statistics.

If Select * into part is not problematic then go for it.

Also you have to create Covering index on art.ArticleConcept table.