SQLServer中，大表的数据删除操作，单次删除和批量多次删除产生的事务日志的差别

 
1，应用场景

SQLServer中一个大表（测试环境千万级，实际情况下会更多，达到10亿级），删除其中大部分数据。然后测试分批多次删除和一次性全部删除产生的transaction log的日志大小的问题。另：受限于相关的表做了复制分发，因此无法通过备份部分数据后truncate table的方式来实现，也无法通过新建一个表，通过rename的方式来交换实现，这两种方式不再考虑范围之内，也不是本文的重点。  以下为生成测试数据库前置条件：1，数据库为SQLServer 2019标准版2，测试文件所在的磁盘为普通普通SSD（非NVME类型SSD）3，创建两个一样的数据库，保持数据库的初始大小，增长大小一致；日志的初始大小，增长大小一致，两个数据库均为full recovery模式4，两个库中定义完全一样的表，写入完全一致的测试数据，最低1000W起步，否则测试意义不大 以下为测试脚本

1. USE [master]
2. GO
3. CREATE DATABASE [TransactionTest_01] ON PRIMARY
4. ( NAME = N'TransactionTest_01_Data', FILENAME = N'D:\MSSQL\Data\TransactionTest_01.mdf' , SIZE = 32768KB , MAXSIZE = UNLIMITED, FILEGROWTH = 32768KB)
5. LOG ON
6. ( NAME = N'TransactionTest_01_Log', FILENAME = N'D:\MSSQL\Log\TransactionTest_01_Log.ldf' , SIZE = 65536KB , MAXSIZE = UNLIMITED, FILEGROWTH = 131072KB )
7. GO
9. USE [master]
10. GO
12. CREATE DATABASE [TransactionTest_02] ON PRIMARY
13. ( NAME = N'TransactionTest_02_Data', FILENAME = N'D:\MSSQL\Data\TransactionTest_02.mdf' , SIZE = 32768KB , MAXSIZE = UNLIMITED, FILEGROWTH = 32768KB)
14. LOG ON
15. ( NAME = N'TransactionTest_02_Log', FILENAME = N'D:\MSSQL\Log\TransactionTest_02_Log.ldf' , SIZE = 65536KB , MAXSIZE = UNLIMITED, FILEGROWTH = 131072KB )
16. GO
18. use [TransactionTest_01]
19. go
21. create table test01
22. (
23.     c1 int identity(1,1),
24.     c2 varchar(100),
25.     c3 varchar(100),
26.     c4 varchar(100),
27.     c5 varchar(100),
28.     c6 varchar(100),
29.     c7 varchar(100),
30.     c8 varchar(100),
31.     c9 varchar(100),
32.     c10 datetime2,
33.     constraint pk_test01 primary key(c1)
34. );
36. use [TransactionTest_02]
37. go
39. create table test01
40. (
41.     c1 int identity(1,1),
42.     c2 varchar(100),
43.     c3 varchar(100),
44.     c4 varchar(100),
45.     c5 varchar(100),
46.     c6 varchar(100),
47.     c7 varchar(100),
48.     c8 varchar(100),
49.     c9 varchar(100),
50.     c10 datetime2,
51.     constraint pk_test01 primary key(c1)
52. );
53. go
57. --生成测试数据，这里是5千万，如果磁盘太慢或者其他原因，可以减小@i参数的值，少生成一些数据
58. declare @i int = 0
59. begin tran;
60. while @i<50000000
61. begin
62.     declare @var varchar(100) = newid();
63.     declare @currentdatetime datetime2 = sysdatetime();
65.     insert into TransactionTest_01..test01 values (@var,@var,@var,@var,@var,@var,@var,@var,@currentdatetime);
66.     insert into TransactionTest_02..test01 values (@var,@var,@var,@var,@var,@var,@var,@var,@currentdatetime);
67.    
68.     set @i = @i + 1;
70.     if @i%10000=0
71.     begin
72.         commit;
73.         begin tran;
74.     end
75. end
77. if @@trancount > 0
78.     commit;
79. go
82. --验证数据的一致性
83. select count(1) from TransactionTest_01..test01 with(nolock);
84. select count(1) from TransactionTest_02..test01 with(nolock);
86. select top 100 * from TransactionTest_01..test01 with(nolock) order by c1 desc;
87. select top 100 * from TransactionTest_02..test01 with(nolock) order by c1 desc;
90. 场景1：
91. --批量删除，执行前重启SQLServer服务
92. while 1>0
93. begin
94.     delete top (5000) from TransactionTest_01..test01 where c1 < 15740000
95.     if @@rowcount=0
96.     begin
97.         break
98.     end
99. end
102. 场景2：
103. --全量删除，执行前重启SQLServer服务
104. delete from TransactionTest_02..test01 where c1 < 15740000

复制代码

以下为生成的测试数据（受时间限制，仅生成了16百万的数据）：
 
2，测试数据

两个库中，生成测试数据后，测试表的数据库完全一致

 
3，测试数据库的文件大小

两个库中，生成测试数据之后，测试数据的数据文件和日志文件完全一致。

 
4，删除数据后的事务日志大小（删除前后均不做事务日志备份）

4.1，批量删除测试库1中的数据，事务日志增加了400MB左右

4.2，整体删数测试库2中的数据，事务日志增加了31G左右

 
5，结论

对于完全一样的表，通过分批多次删除，和一次性删除，删除同样多的数据的情况下：
1，事务日志增长量400MB VS 31000MB，
2，执行耗时，115秒 VS 265秒

 
6，相关参考

这里有一篇相关性的文章，已经有十几年了，Break large delete operations into chunks（https://sqlperformance.com/2013/03/io-subsystem/chunk-deletes），也提到单次删除和分批删出的差异性，这篇文测试了很多的场景。
下面这个场景跟本文的场景类似：full recovery模式，单次删除和分批多次删除的对比，

文章的评论区有人问道：why 'chunks delete' generate less logging但回答是说批量删除备份日志，然后日志重用，这个回答并没有解释，在如上图的测试结果中，为什么没有日志备份的情况下，批量删除的日志量远小于单次删除
最终也没有解释出来根本原因：完整恢复模式下，不做日志备份，删除同样多的数据，单次删除和批量多次删除事务日志差异性的原因。
希望有看到的大佬指点一二

 

来源：程序园用户自行投稿发布，如果侵权，请联系站长删除
免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！