事务日志:用于速度的小事务与大事务

Question

我正在努力学习事务日志是如何工作的，并且已经学习了几天。

当完成许多小事务时，一些操作会变得更快，例如，在小批中删除许多行。请参阅加快大规模删除的方法没有条款

其他操作，例如游标，当一个大的显式事务被环绕在游标周围时会变得更快。这在我看来是矛盾的..。

谁能解释一下吗？

编辑与额外的信息。我的表是行存储，在delete中包含一个集群索引和一个用于谓词的NC索引。

Answer 1

如果使用的是最新版本的Server，则可以在小批处理和一次删除比较中比较每个sys.dm_exec_session_wait_stats的会话等待。启用查询存储和比较小批删除中的逻辑读取、物理读取和CPU时间等指标以及单个删除也将具有指导意义。

如果没有更多的细节，对你的问题做出更彻底的回答就需要大量的猜测，这样就不会有多大用处。例如，前面提到的问题没有指定表行存储或列存储是否已分区，如果表是行存储表，表是否具有主键或聚集索引，删除是如何分解为小批，还是大小被选择为“小”(只是猜测还是经过测试的许多候选项之一？)。

Aaron Bertrand在下面的博客文章中仔细考虑了其中的许多因素--在某些情况下，存储子系统的特性可以使单个删除比许多小的删除更快，这似乎让他感到惊讶。

删除SQLServer2019中大量记录的最快方法--12月3日https://www.mssqltips.com/sqlservertip/6238/fastest-way-to-delete-large-number-of-records-in-sql-server/

这里有一个可能的场景，可以解释您所看到的性能差异--但是还有许多其他可能的场景。

很有可能事务日志的大小已经足够大，不需要增长来记录单个会话执行小批删除的许多小的、不重叠的事务。同时，单个大型delete很可能需要事务日志多次增长才能记录其活动。事务日志不允许利用即时文件初始化(如果事务日志自动增长)，它必须在完成写入之前写入整个新的增长区域。因此，许多支持单个大事务的小事务日志自动增长可能会加剧写量等待，并大大增加单个大型删除的持续时间。

Answer 2

就像大多数事情一样，这取决于..。

对一组表打开事务的时间越长，这些表的锁定时间就越长。锁定表会导致与试图从该表读取和写入该表的其他查询发生争用。因此，将事务分解为多个较小的事务可以减少那些表被锁定的连续运行时。它还可以更好地隔离，只锁定需要为代码的该部分绝对锁定的表。

但是，许多关系问题最好以关系方式解决(与迭代或过程不同)，在创建和提交(或回滚)多个事务以及为那些事务(有时单个事务最有利于最小化的事务)提取适当锁时，还有其他停机时间。

例如，如果您试图更新表中的100行，在大多数情况下，更新单个事务/单个UPDATE语句中的所有100行要比取出100个事务并逐行遍历表100次要快得多。

通常情况下，您试图运行的查询将以最少的事务运行最快，但对于访问这些表的其他服务器/其他查询而言，可能会导致该查询中涉及的表的争用量最大。通过将查询/逻辑分解为多个事务，您可能会减少服务器其余部分对这些表的总体争用，但在被分解为多个事务的特定查询中可能会导致某些性能下降。

因此，所要做出的决定将取决于该特定查询的快速运行对于涉及查询所使用的相同表的其他服务器上的繁忙程度有多重要，以及它们能够等待多长时间来执行自己的操作。