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 执行完整日志记录.
结论 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
来源: https://www.cnblogs.com/ljhdo/p/4607188.html