SQL Server 最小日志记录

时间:2022-03-18 14:42:08

SQL Server之所以记录事务日志,首要目的是为了把失败或取消的操作还原到最原始的状态,但是,并不是所有的操作都需要完全记录事务日志,比如,在一个空表上放置排他锁,把大量的数据插入到该空表中。即使插入操作在任意时刻失败,只需要把清空表,就可以把表还原,根本不需要记录插入的详细数据。在表上放置排他锁的目的,是为了阻止其他人更新该表,当插入失败时,只需要清空表就还原到最原始的状态。

最小化日志记录仅记录恢复事务所需的信息,而不支持任意时间点恢复,也就是说,在最小化日志记录操作时,SQL Server也会记录事务日志,但是仅记录回滚事务所需的有限信息。“有限信息”是指,仅把分配的页面记录在事务日志中,而没有记录这些页面包含的实际数据,因此保持了较小的事务日志文件的大小。

一,最小日志操作

在FULL还原模式下,所有的大容量操作都会完全记录事务日志,在进行大容量数据插入时,最小化日志记录更有效率,减少了事务日志空间在大容量操作时暴增的可能性,但是,如果在最小化日志记录生效时数据库已损坏或丢失,那么无法把数据库恢复到故障点。

在最小化日志记录期间执行大容量数据插入,虽然数据插入不会记录在事务日志中,但是,对于每次为表分配的区(8个物理地址连续的Page)都会记录在事务日志中。不是所有的操作都能实现最小化日志记录,最小化日志操作的类型:

  • 大容量导入操作(Bulk Import Operations)包括 BULK INSERT、BCP和 INSERT SELECT
  • SELECT INTO
  • 索引操作:CREATE INDEX、ALTER INDEX REBUILD、DROP INDEX

有意思的是,TRUNCATE 并不是最小化日志记录操作,在任何还原模式下,TRUNCATE 都完整记录事务日志的,并能够还原到任意时间点,不过TRUNCATE记录日志的效率更高,采用deferred-drop 机制来记录日志。

二,触发最小日志的条件

测试用例的环境是SQL Server 2017版本,在 SIMPLE或BULK_LOGGED还原模式下做测试。

实际上,要在执行大容量插入时实现最小化日志记录,必须满足五个条件:

  • 数据库处于SIMPLE或BULK_LOGGED还原模式
  • 表级锁定,推荐使用表 hint 显式上锁:with(tablock)
  • 不是复制表
  • 不是内存优化表
  • 在满足前四个条件的基础上,有如下结论:

一个表是否可以进行最小化日志记录还取决于该表是否已建立索引,如果是,则取决于该表是否为空。

结论1:表没有索引,Data Page执行最小化日志记录。

结论2:表没有聚集索引,但是有非聚集索引,Data Page执行最小化日志记录。

  • 当表是空的时,Index Page执行最小化日志记录
  • 当表有数据时,Index Page执行完整日志记录

对于使用分Batch插入的情况,当表是空的,对于第一个Batch插入,Data Page和Index Page都执行最小化日志记录;从第二个Batch开始,Data Page执行最小化日志记录,而Index Page执行完整日志记录。

结论3:表有聚集索引

  • 当表有聚集索引,并且是空表时,Data Page和Index Page都执行最小化日志记录。
  • 当表有聚集索引,并且有数据时,Data Page和Index Page都执行完整日志记录。

对于使用分Batch插入的情况,当表是空的,对于第一个Batch插入,Data Page和Index Page都执行最小化日志记录;从第二个Batch开始,Data Page执行最小化日志记录,而Index Page执行完整日志记录。

SQL Server 最小日志记录

结论4,从表中可以看出:

  • 索引页的分配都是Fully Logged,
  • 堆表的数据页更新都是Min Logged,
  • 只有当表是聚集索引时,数据页的更新才是Fully Logged的,实际上,BTree表就是索引本身。

三,索引操作中的最小化日志

从上节中的结论4中知道,索引页的分配都是Fully Logged,索引页的回收(deallocation )也都是Fully Logged。在特定的情况下,执行CREATE INDEX、ALTER INDEX REBUILD 和 DROP INDEX能够激发数据页的最小化日志记录,索引的重建(REBUILD)相当于先删除索引,再创建索引。

比如,创建索引相当于向有数据的表中插入数据,索引页是Full Logged,数据表根据结论4来判断数据页是Full Logged或Min Logged。

四,延迟删除

TRUNCATE TABLE简单来说,是通过回收已分配的数据页来移除数据,并且只把回收的数据页记录在事务日志中。

DROP TABLE 和 TRUNCATE TABLE 都是完整记录日志的操作,不过日志不是立即创建,而是延迟记录,这是由延迟删除(deferred drop)的机制来实现的。当一个表被 drop 或 truncate 时,属于该表的所有数据页都会被系统标记为回收,并把标记为回收的数据页和区放置在延迟删除队列(deferred-drop queue)中,该数据页或区实际上并没有释放,只是标记为回收(deallocation )。延迟删除机制通过回收表的数据页,从而模拟drop 或 truncate操作立即完成后的效果,这个过程仅仅产生很少的日志记录。

但是延迟删除的后台处理程序(deferred-drop background task)每隔几秒钟就会执行一次,并以小批量的方式回收放置在延迟删除队列(deferred-drop queue)中的所有Page和Extent,从而确保操作不会耗尽内存。回收空间的操作是完全记录日志的,不过,释放一个充满数据或索引记录的页面,并不会记录个别数据行的删除。相反,整个页面只是在相关的PFS(Page Free Space)分配字节图中标记为已取消分配。

从SQL Server 2000 SP3开始,执行表的DROP或TRUNCATE时,只会看到一些正在生成的日志记录。如果等待一分钟左右,然后再次查看事务日志,您将看到deferred-drop操作已经生成了成千上万的日志记录,每个日志记录都表示回收一个Page或Extent。

 

参考文档:

Operations that can be minimally logged

Prerequisites for Minimal Logging in Bulk Import

SQL Server: Understanding Minimal Logging Under Bulk-Logged Recovery Model vs. Logging in Truncate Operation

SQL Server: An Examination of Logging in Truncate Table Statement and Its Comparison With Delete Statement

The Myth that DROP and TRUNCATE TABLE are Non-Logged