Sql-server – Why does the DELETE command require huge amount of temporary run storage

deletedisk-spacesql serversql server 2014

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)

Execution plan

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'

Best Answer

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

numbered

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.

Related Question