WITH TABLOCK在完整恢复模式下提升插入性能的原因探究

你观察到的现象核心在于：完整恢复模式下虽然不支持最小日志记录，但WITH (TABLOCK)带来的锁机制、日志写入、存储引擎层面的批量优化，依然能大幅降低插入开销，具体原因拆解如下：

• 锁开销的大幅减少
  不使用TABLOCK时，1000万行的插入会逐行/逐页申请行级锁或页级锁，这会带来巨量的锁申请、释放操作，还可能引发锁升级、锁等待甚至死锁，上下文切换和锁管理的CPU开销极高。而WITH (TABLOCK)会直接获取表级排他锁（X锁），全程仅需一次锁申请，彻底规避了细粒度锁的管理成本。

• 日志写入的批量优化
  完整恢复模式要求记录完整日志，但TABLOCK允许SQL Server以批量日志块的方式写入日志，而非逐行生成日志记录。这种批量写入的磁盘IO效率远高于零散的行级日志，减少了磁盘IO的次数和寻址开销，即使日志量没有减少，写入速度也会大幅提升。

• 存储引擎的批量操作优化
  持有表级锁时，SQL Server会启用针对批量操作的优化策略：比如一次性分配连续的扩展区（Extent）而非零散的数据页，减少页面分配的开销；跳过部分行级的一致性检查，降低CPU消耗；避免频繁的页拆分，提升数据存储的连续性。

• 索引维护的批量处理
  当表存在索引时，无TABLOCK的插入会逐行更新索引B树，每插入一行就要调整索引结构，频繁的索引页拆分和写入会带来极大开销。而WITH (TABLOCK)下，SQL Server会先收集所有插入数据的索引键值，再一次性完成索引的批量更新，大幅减少索引页的拆分次数和IO操作，所以即使有索引，耗时仍远低于无TABLOCK的场景。

内容的提问来源于stack exchange，提问作者variable