主页 / dba / 问题 / 190140
Accepted
Alexei
Asked: 2017-11-06 02:23:49 +0800 CST

为什么我的 DELETE 命令需要大量的临时运行存储空间？

  • 772

我正在尝试对使用的表执行清理操作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”，数据库的事务日志...已满

sql-server sql-server-2014

3 个回答

  1. Best Answer

    查询计划中有七个运算符可能溢出到 tempdb。我在下面给它们编号：

    编号的

    查询优化器将子查询select ArticleId from art.Article where PublishDate < @deleteDate实现为两个非聚集索引之间的连接。连接是哈希连接，需要在标签1处构建哈希表。哈希表有可能溢出到 tempdb。对于您的查询，哈希表只有大约 100k 行，因此不太可能成为问题。

    ArticleConcept和之间的连接Article实现为合并连接。两个连接输入都需要对连接进行排序，这会导致在标签2处看到的排序。这种排序只需要处理大约 10 万行。

    为了提高删除的性能，在标签3处进行了排序。数据将按照表的聚集索引的键的顺序排序。您要删除大约 1200 万行，所以我希望对 1200 万行的聚集键进行排序。这可能会溢出到 tempdb。

    删除的目标表具有非聚集索引。查询优化器有几种不同的方法来实现对索引的更新。它选择一个广泛的、每个索引的更新。这是基于成本完成的，并且很可能会发生，因为您要从目标表中删除大部分行。标签4处的表假脱机包含所有索引键以及聚簇索引键。它将存储 1200 万行并将写入 tempdb。

    标签5、6和7处的排序是按照每个非聚集索引的索引键和聚集索引键的顺序对数据进行排序。这些类型很可能会溢出到 tempdb。

    所有这些泄漏加起来。如果您在磁盘上有某种 1 GB 的数据，并且这种数据溢出到磁盘，它不一定会正好占用 1 GB 的 tempdb 空间。根据我的经验，它在 tempdb 中通常需要比在磁盘上更多的空间。

    即使查询没有失败，它仍然不是最佳方法。从聚集索引和三个非聚集索引的 1400 万行表中删除 1200 万行是一项大量工作。将要保留的行插入到另一个表中、在该表上构建非聚集索引并就地切换表会更有效。正如您自己所见，在删除之前删除非聚集索引并在删除之后重新创建它们可能就足够了。此处描述的变通办法只能在最终用户不访问数据的维护窗口期间完成。

    • 4

  2. 您需要对删除进行批处理，这样您就不会锁定表并填满事务日志。

    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

    您可以尝试更大的批量大小（我通常使用 5000-50000），但这是一个很好的起点。在选择批量大小时，您必须小心锁定升级尝试。

    通过将 s 列表发送到临时表，您可能还会看到一种改进，ArticleId这样您只需扫描art.Article一次。

    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
    • 3

  3. 主要问题是没有日期列art.ArticleConcept。

    此外，您应该始终共享两个表的表结构。

    Bacthwise delete也不错。

    我认为@Joe提出了如下相同的方法

    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)

    上述方法的主要优点是，您没有回收空间或重建索引或重建统计信息。

    如果Select * into零件没有问题，那就去吧。

    Covering index您还必须在art.ArticleConcept表上创建。

    • 1

相关问题