Why does my DELETE command require huge amount of temporary run storage? 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 &lt; @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 &lt; @deleteDate    -- 111450
select count(1) from art.ArticleConcept where ArticleId IN (select ArticleId from art.Article where PublishDate &lt; @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 &lt;X64&gt; (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'
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 &gt; 0)
BEGIN

  delete top 1000 art.ArticleConcept where ArticleId IN (select ArticleId from art.Article where PublishDate &lt; @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 &lt; @deleteDate;

DECLARE @RC INT = 1;

WHILE (@RC &gt; 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