2

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'

  • 1
    Batch your modifications! – Erik Darling Nov 5 '17 at 13:02
  • If you are trying to delete 12M out of 14M rows from a table, it might be a lot faster to simply select the 2M you want to keep into a new table with the same structure and perhaps perform a partition switch after truncating your source. I believe the same indexes would have to also exist for this new table. There are some restrictions for the switch but it might suit your needs. – Queue Mann Jul 17 at 18:45
  • @QueueMann - you are certainly right. However, I was curious how removing some X MB from a table requires much more space. Joe Obbish provided a very nice explanation. – Alexei Jul 17 at 19:16
2

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.

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
  • Yes, it works and I think this is the best solution to avoid locking the table too much. I have also experimented a little, by adding a DateTime2 column, populating it with values from the parent table, adding a clustered index on it and removing data based on this column. I have also tried the same long DELETEs after dropping all seconday indexes and it worked, but the lock time is quite big (actual DELETE + indexes rebuild take several minutes). Clearly, your solution is the way to go. – Alexei Nov 5 '17 at 18:12
  • @Alexei , have you check space_use before delete and after delete operation.As far as i know you have to rebuild your index after delete,to reclaim space – KumarHarsh Nov 7 '17 at 11:02
  • @KumarHarsh - Yes, I have checked the space. Since this is a small database (< 10GB), I just shrink the database and reclaim it (I do not know if it is the best thing to do, but it is the simplest and takes a max 1-2 minutes). However, by initial problem is related to performing such a big delete. – Alexei Nov 7 '17 at 19:37
1

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.