删除大量行的速度非常慢- SQL Server

Question

我的数据库表包含大约300万条记录。当我删除大量的行，大约400,000条记录时，事务永远不会完成。

该表未分区，并且数据库正在Sql Server 2012 Standard Edition上运行。我使用Nhibernate作为ORM。

如何使事务处理速度更快？

这里是表的创建脚本

/****** Object:  Table [dbo].[ES_DirectorDataParameters]    Script Date: 03/10/2016 4:10:30 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[ES_DirectorDataParameters](
    [DDP_ID] [numeric](18, 0) IDENTITY(1,1) NOT NULL,
    [DP_Name] [varchar](255) NOT NULL,
    [D_ID] [numeric](18, 0) NOT NULL,
    [DDP_DisplayName] [varchar](255) NULL,
    [DDP_Visibility] [varchar](50) NULL,
    [DDP_Replicable] [numeric](18, 0) NOT NULL CONSTRAINT [DF_ES_DirectorDataParameters_DD_Replicable]  DEFAULT ((1)),
 CONSTRAINT [PK_ES_DirectorDataParameters] PRIMARY KEY CLUSTERED 
(
    [DP_Name] ASC,
    [D_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

ALTER TABLE [dbo].[ES_DirectorDataParameters]  WITH CHECK ADD  CONSTRAINT [FK_ES_DirectorDataParameters_ES_DataParameters] FOREIGN KEY([DP_Name])
REFERENCES [dbo].[ES_DataParameters] ([DP_Name])
GO

ALTER TABLE [dbo].[ES_DirectorDataParameters] CHECK CONSTRAINT [FK_ES_DirectorDataParameters_ES_DataParameters]
GO

ALTER TABLE [dbo].[ES_DirectorDataParameters]  WITH CHECK ADD  CONSTRAINT [FK_ES_DirectorDataParameters_ES_Directors] FOREIGN KEY([D_ID])
REFERENCES [dbo].[ES_Directors] ([D_ID])
GO

ALTER TABLE [dbo].[ES_DirectorDataParameters] CHECK CONSTRAINT [FK_ES_DirectorDataParameters_ES_Directors]
GO

下面是我在执行delete (删除大约200000行)时在活动监视器中找到的delete语句

DELETE FROM ES_DirectorDataParameters WHERE DDP_ID = @p0

注意:为列DDP_ID创建集群索引对删除性能略有帮助

谢谢

Answer 1

让我开始吧: 400.000条记录很小。我经常删除6400万条记录。

如何使交易速度更快？

与提高SQL Server的总体速度相同:您提供的IO功能比我想象的要多得多。

• 将数据库、临时数据库和登录拆分到不同的硬盘中。哦，把它做成固态硬盘，或者至少是有备份缓冲区的东西。

SQL的生死取决于IO能力，在过去15年左右的时间里，每个人都在抱怨“大型”数据操作(实际上是很小的)的性能，我曾与之交谈过的人总是在完全不足以完成任何真正的数据库工作的硬件布局上运行SQL Server。我们谈论一个滑稽的水平，比如“我想用一辆卡车赢得一级方程式赛车”类型的差异。

为了让您了解我的布局(对于6400万行操作)：2个Raid 5中的6个SSD用于数据，4个SSD在Raid 10中用于临时数据库，2个SSD镜像用于日志。

还要确保你有足够的内存-通常你应该在内存中保留数据的活动集，以避免命中磁盘。

显然，还要检查是否存在适当的索引。

Answer 2

在活动监视器中找到的delete语句

DELETE FROM ES_DirectorDataParameters WHERE DDP_ID = @p0

您的一条评论提到您使用了NHibernate，这表明NHibernate实际上正在发送200000条单独的delete语句。

因此，您的问题不在于SQL查询的性能，而在于单个查询的数量。

您需要用纯SQL重写批量删除逻辑，以便可以将删除表示为一条语句。

如果您需要有关SQL的帮助，请描述要删除哪些行的逻辑，也许我可以提供帮助。

Answer 3

如果你的硬件太慢了，看看TomTom的答案。

否则..。

如果每行的大小都很大，那么事务日志可能就是问题所在。特别是如果您的行是10KB或更大，那么删除100,000行可能是一个多GB的日志记录操作。

检查事务日志文件的自动增长大小是否合理(可能是100MB)，以便不需要频繁地自动增长。

检查您的数据库恢复模式。如果它不是“简单的”，那么日志将存储所有的删除，直到您的下一次备份。如果它是“简单的”，那么在您提交事务之前，它只需要存储删除操作。

如果您不介意将删除操作拆分到多个事务中，这可能有两个好处：

• 您可以中断(和重新启动)删除操作，而不会回滚整个操作。
• 如果恢复模式很简单，则会减小事务日志的大小。

此脚本将任务拆分为1000个删除的区块。它假设您的表只有一个列主键。

DECLARE @RC INT;
SET @RC = 1;

WHILE (@RC > 0)
BEGIN
    BEGIN TRAN
        DELETE FROM YOUR_TABLE
        WHERE ID IN (
            SELECT TOP(1000) ID 
            FROM YOUR_TABLE AS T
            WHERE {your criteria for deletion using alias T}
        );
        SET @RC = @@ROWCOUNT;
    COMMIT
END

当我必须删除许多包含已上传文件的blobs的行时，我使用了类似的技术。