yegnasew Asked: 2020-07-10 05:36:58 +0800 CST2020-07-10 05:36:58 +0800 CST 2020-07-10 05:36:58 +0800 CST 截断 AG 中包含 170 亿行的表 772 我需要截断一个包含 170 亿行的表,该表位于作为 AG 一部分的数据库中。 此操作对 AG 延迟和日志备份大小有何影响? 有推荐的方法吗? sql-server availability-groups 2 个回答 Voted Josh Darnell 2020-07-10T08:52:49+08:002020-07-10T08:52:49+08:00 记录范围 生成的日志数据量(并因此通过网络发送到您的其他 AG 节点)取决于您的170亿行表中的行数。 与TRUNCATE做. _ 但它仍然可能很重要,具体取决于您的基础架构和期望。DELETE 考虑dbo.VotesStack Overflow 示例数据库中的表: 每行是 28 个字节。SQL Server 中的一个页面为 8 KB(8,192 字节),因此您可以在一个页面上放置大约 292 行。这并不完全正确,因为页面和行都有开销,但对于这个示例来说,这是一个不错的近似值。 这意味着大约需要 58,219,178 页来保存所有 170 亿行。执行DROPorTRUNCATE时,后台任务会释放扩展区(8 页的组)。这些释放中的每一个都被记录下来。这意味着通过截断该表将创建大约 7,277,397 条日志记录。 测试dbo.Votes 在将恢复模式设置为完整并进行完整备份和日志备份以初始化备份链之后,我在该示例数据库的副本上进行了尝试。该dbo.Votes表有 10,146,802 行。根据我们之前的计算,这应该是大约 34,749 页,或 4,343 个范围。 实际上,该表使用分配给它的 47,721 页(由于前面提到的开销),即 5,965.125 个扩展区。 现在我将TRUNCATE表: TRUNCATE TABLE dbo.Votes; 这立即完成,但我最终得到了 17,605 条日志记录。看起来每个范围确实有 3 条日志记录(2 条用于更新 IAM 和 GAM 页面,1 条用于更新 PFS 页面以释放数据页面)。 这些日志记录总共只使用了约 1.28 MB 的日志文件。但是你的真实表的行数是这个的 1,600 倍,而且你的行数可能更大。这可能意味着生成超过 1 GB 的日志数据并通过网络发送到每个副本。 如果您在此表上有非聚集索引,数据量会进一步增长,这些索引以相同的方式记录。 行大小有很大的不同 另一个案例研究可能是dbo.Comments表格。它有 3,907,472 行,但每行有 1,424 字节长(最大值 -Text列是nvarchar(700))。 尽管行数明显少于dbo.Votes,但该表分配了 176,722 页。 TRUNCATE生成63,792dbo.Comments条日志记录和 4.86 MB 的日志数据。 如果您的实际行大小在此范围内更大,则可能超过 7.5 GB 的日志数据。 该怎么办 也许您的基础架构和日志文件可以轻松处理数 GB 的数据 - 如果您有一个 17 B 行的表,它们似乎应该如此!但我认为值得一提的是,流量不一定微不足道,因为现有的答案并没有提出这一点。 如果可以,请在非生产环境中进行测试。测量之前和之后的日志文件使用情况,并确保您的 prod 基础架构已设置为处理该数量的数据。确保日志文件已预先调整大小以处理此截断 - 在此操作中间发生自动增长会大大减慢速度,并导致阻塞。 如果您无法测试,请尽最大努力估计会产生什么影响。使用类似这样的查询来获取表中的页数。然后将其除以 8(得到范围的数量)并乘以 3 得到大约的日志记录数。 我的测试有大约 70 字节的平均日志记录大小,但我不知道这是否是典型的。但是您可以尝试将近似日志记录乘以 70,以获得截断产生的潜在日志字节数。 混合使用 AG,您还可以使用日志流压缩。我没有使用这些跟踪标志,所以我真的只是提到它是另一个可以调整的旋钮。 如果您发现该TRUNCATE方法太不可预测,或者对您的系统来说太多,您总是可以DELETE批量使用 normal。这总共使用了更多的日志,但您可以将其分布在您喜欢的任何时间段内。但是,如果您走这条路,请确保在编写批处理脚本时要小心。 Best Answer Aleksey Vitsko 2020-07-10T06:08:02+08:002020-07-10T06:08:02+08:00 Truncate table命令立即截断行并且不将删除的行写入事务日志文件 通常Truncate table会立即执行,并且 AG 中的副本之间没有明显的网络流量,因此不会有明显的日志备份等,与您执行时相比Delete from。但是,可能会有明显的流量和明显的日志备份,因为 170 亿行是很多。 PS 考虑在做 truncate 之前备份数据库并将备份保存到存档,这样你可以在以后恢复 170 亿表,如果需要的话
记录范围
生成的日志数据量(并因此通过网络发送到您的其他 AG 节点)取决于您的170亿行表中的行数。 与
TRUNCATE
做. _ 但它仍然可能很重要,具体取决于您的基础架构和期望。DELETE
考虑
dbo.Votes
Stack Overflow 示例数据库中的表:每行是 28 个字节。SQL Server 中的一个页面为 8 KB(8,192 字节),因此您可以在一个页面上放置大约 292 行。这并不完全正确,因为页面和行都有开销,但对于这个示例来说,这是一个不错的近似值。
这意味着大约需要 58,219,178 页来保存所有 170 亿行。执行
DROP
orTRUNCATE
时,后台任务会释放扩展区(8 页的组)。这些释放中的每一个都被记录下来。这意味着通过截断该表将创建大约 7,277,397 条日志记录。测试
dbo.Votes
在将恢复模式设置为完整并进行完整备份和日志备份以初始化备份链之后,我在该示例数据库的副本上进行了尝试。该
dbo.Votes
表有 10,146,802 行。根据我们之前的计算,这应该是大约 34,749 页,或 4,343 个范围。实际上,该表使用分配给它的 47,721 页(由于前面提到的开销),即 5,965.125 个扩展区。
现在我将
TRUNCATE
表:这立即完成,但我最终得到了 17,605 条日志记录。看起来每个范围确实有 3 条日志记录(2 条用于更新 IAM 和 GAM 页面,1 条用于更新 PFS 页面以释放数据页面)。
这些日志记录总共只使用了约 1.28 MB 的日志文件。但是你的真实表的行数是这个的 1,600 倍,而且你的行数可能更大。这可能意味着生成超过 1 GB 的日志数据并通过网络发送到每个副本。
如果您在此表上有非聚集索引,数据量会进一步增长,这些索引以相同的方式记录。
行大小有很大的不同
另一个案例研究可能是
dbo.Comments
表格。它有 3,907,472 行,但每行有 1,424 字节长(最大值 -Text
列是nvarchar(700)
)。尽管行数明显少于
dbo.Votes
,但该表分配了 176,722 页。TRUNCATE
生成63,792dbo.Comments
条日志记录和 4.86 MB 的日志数据。如果您的实际行大小在此范围内更大,则可能超过 7.5 GB 的日志数据。
该怎么办
也许您的基础架构和日志文件可以轻松处理数 GB 的数据 - 如果您有一个 17 B 行的表,它们似乎应该如此!但我认为值得一提的是,流量不一定微不足道,因为现有的答案并没有提出这一点。
如果可以,请在非生产环境中进行测试。测量之前和之后的日志文件使用情况,并确保您的 prod 基础架构已设置为处理该数量的数据。确保日志文件已预先调整大小以处理此截断 - 在此操作中间发生自动增长会大大减慢速度,并导致阻塞。
如果您无法测试,请尽最大努力估计会产生什么影响。使用类似这样的查询来获取表中的页数。然后将其除以 8(得到范围的数量)并乘以 3 得到大约的日志记录数。
我的测试有大约 70 字节的平均日志记录大小,但我不知道这是否是典型的。但是您可以尝试将近似日志记录乘以 70,以获得截断产生的潜在日志字节数。
混合使用 AG,您还可以使用日志流压缩。我没有使用这些跟踪标志,所以我真的只是提到它是另一个可以调整的旋钮。
如果您发现该
TRUNCATE
方法太不可预测,或者对您的系统来说太多,您总是可以DELETE
批量使用 normal。这总共使用了更多的日志,但您可以将其分布在您喜欢的任何时间段内。但是,如果您走这条路,请确保在编写批处理脚本时要小心。Truncate table
命令立即截断行并且不将删除的行写入事务日志文件通常
Truncate table
会立即执行,并且 AG 中的副本之间没有明显的网络流量,因此不会有明显的日志备份等,与您执行时相比Delete from
。但是,可能会有明显的流量和明显的日志备份,因为 170 亿行是很多。PS 考虑在做 truncate 之前备份数据库并将备份保存到存档,这样你可以在以后恢复 170 亿表,如果需要的话