為什麼我的 DELETE 命令需要大量臨時執行儲存？

我正在嘗試使用表對錶執行清理操作DELETE，但收到以下錯誤：

無法為數據庫“tempdb”中的對象“dbo.SORT 臨時執行儲存：140767697436672”分配空間，因為“PRIMARY”文件組已滿。通過刪除不需要的文件、刪除文件組中的對象、向文件組添加其他文件或為文件組中的現有文件設置自動增長來創建磁碟空間。

在執行之前DELETE我有超過11G的可用磁碟空間。發出錯誤時，該分區上幾乎沒有任何內容。上下文資訊如下：

1）有問題的查詢：

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

2. 涉及表的基數

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. 索引

-- 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. 伺服器

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）執行計劃（估計）

我知道我正在執行一個大的 DELETE，但我不明白為什麼它需要這麼多空間來執行它：整個ArticleConcept表的空間小於 2GB（保留空間），但從中刪除記錄需要超過 11GB。

**問：**為什麼我的 DELETE 命令需要大量的臨時執行儲存？

我已經刪除了所有二級索引，我可以執行DELETE. 但是，為什麼在擁有它們時需要更多的空間來執行，DELETE這對我來說看起來很奇怪。

我正在嘗試從 14,624,589 條記錄中刪除 12,153,045 條（很多）。我沒有監控事務日誌，但是一旦我收到與之相關的錯誤：

由於“ACTIVE_TRANSACTION”，數據庫 … 的事務日誌已滿

查詢計劃中有七個可能溢出到 tempdb 的運算符。我在下面給它們編號：

子查詢select ArticleId from art.Article where PublishDate < @deleteDate由查詢優化器實現為兩個非聚集索引之間的連接。該連接是一個雜湊連接，它需要在標籤1處建構一個雜湊表。雜湊表有可能溢出到 tempdb。對於您的查詢，雜湊表只有大約 100k 行，因此不太可能成為問題。

ArticleConcept和之間的連接Article被實現為合併連接。兩個連接輸入都需要為連接進行排序，這導致在標籤2處看到的排序。這種排序只需要處理大約 100k 行。

在標籤3處進行排序以提高刪除的性能。數據將按照表的聚集索引鍵的順序進行排序。您正在刪除大約 1200 萬行，所以我希望對 1200 萬行的聚集鍵進行排序。這可能會溢出到 tempdb。

刪除的目標表具有非聚集索引。查詢優化器有幾種不同的方法來實現對索引的更新。它選擇一個廣泛的、每個索引的更新。這是在成本基礎上完成的，並且很可能會發生，因為您要從目標表中刪除大部分行。標籤4處的表假離線包含所有索引鍵以及聚集索引鍵。它將儲存 1200 萬行並將寫入 tempdb。

標籤5、6和7處的排序是按照每個非聚集索引的索引鍵和聚集索引鍵的順序對數據進行排序。這些類型很可能會溢出到 tempdb。

所有這些洩漏加起來。如果您在磁碟上有某種 1 GB 的數據並且該排序溢出到磁碟，則它不一定會消耗 1 GB 的 tempdb 空間。以我的經驗，它在 tempdb 中通常需要比在磁碟上更多的空間。

即使查詢沒有失敗，它仍然不是最佳方法。從聚集索引和三個非聚集索引中刪除 1400 萬行的表中的 1200 萬行是很多工作。將行插入到另一個表中，在該表上建構非聚集索引，並就地切換錶會更有效。正如您自己所見，在刪除之前刪除非聚集索引並在刪除之後重新創建它們可能就足夠了。此處描述的解決方法應僅在最終使用者未訪問數據的維護視窗期間完成。

引用自：https://dba.stackexchange.com/questions/190140